Ask Your Question

cell format date time duration

asked 2016-04-28 22:43:04 +0100

StarMariner gravatar image

Need to find a clean way to change a remainder value of time, from decimal to minutes. ie 0.25 should be 0.25/60 = 15 mins. if I format the result cell C1 , best I can come up with is a fraction with a format code 0.00/60

The following formula I am using gives the duration or time difference between 2 dates and times. I can't seem to seperate my exponent to the left of the decimal from the trig on the right of the decimal point? should be basic. Don't want macros.

Cells A1 = 28/04/2016 , B1 = 06:15 A2 = 29/04/2016 , B2 = 12:25 C1= =SUM((A2+B2)-(A1+A2))*24

C1 looks like 30.10/60 with my format code. So , any ideas how I can remove the /60 part for a clean number. I'd ideally like 40hrs 15Min as a result. If the exp and trig can be seperated,I can do that bit. Thanks for any help.

edit retag flag offensive close merge delete

5 Answers

Sort by » oldest newest most voted

answered 2016-04-28 23:41:31 +0100

erAck gravatar image

Why not simply use the formula =(A2+B2)-(A1+B1) and apply the number format [HH]:MM

Btw, the SUM() function is completely unnecessary in this case, it sums only one argument for which the result is identical to the argument.

edit flag offensive delete link more


old habit, thanks .

StarMariner gravatar imageStarMariner ( 2016-04-29 00:11:07 +0100 )edit

I removed the SUM part, looks a lot nicer now, Thanks :)

StarMariner gravatar imageStarMariner ( 2016-04-29 00:31:56 +0100 )edit

answered 2016-04-29 01:47:41 +0100

Ratslinger gravatar image

Based upon the Date/times in the question, the result is 30hrs & 10 Min. All the calculations present this as a result. If all that remains is a resulting format, why not [HH] "Hrs" MM "Min"?


edit flag offensive delete link more

answered 2016-04-29 00:10:14 +0100

Lupp gravatar image

updated 2016-04-29 00:11:41 +0100

I didn't understand the term "exponent" in the context, and I do not know the term "trig".

You may have a look into the attached.

To separate the integer part and the fractional part of a numeric value, you may use
INT(Number) and MOD(Numb[ask68968TimeAgain001.ods](/upfiles/14618814087968083.ods)er;1) respectively.

Sorry! Just found that some answers crossed while I was off for a short time.

edit flag offensive delete link more


Sorry my bad, old school processor terms :

StarMariner gravatar imageStarMariner ( 2016-04-29 00:22:54 +0100 )edit

answered 2016-04-28 23:52:22 +0100

karolus gravatar image

Why not:


with Formatcode[HH]:MM

edit flag offensive delete link more


I tried that , didnt work for me. It's limited to 24 hours , I needed the days as hours , 2 days being 48hrs ectc..Thanks

StarMariner gravatar imageStarMariner ( 2016-04-29 00:12:35 +0100 )edit

It is not. Note the difference between [HH]:MM and HH:MM

erAck gravatar imageerAck ( 2016-05-02 18:35:14 +0100 )edit

answered 2016-04-29 00:08:14 +0100

StarMariner gravatar image

updated 2016-04-29 00:14:38 +0100

Oh well , I have a solution: Have 2 cells, the first cell M5=SUM( (K5+L5)-(I5+J5))*24 # this is the full result N5=MOD(M5,1)*60 # this is the right side as a decimal .18 multiplied by 60 =11 minutes.

Now above that I have 2 cells for the title, which are merged. Cell formating is just numbers with no trailing decimals.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-04-28 22:43:04 +0100

Seen: 1,414 times

Last updated: Apr 29 '16