# cell format date time duration

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 close merge delete

Sort by » oldest newest most voted

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.

more

old habit, thanks .

( 2016-04-29 00:11:07 +0200 )edit

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

( 2016-04-29 00:31:56 +0200 )edit

Why not:

=A2+B2-A1-B1


with Formatcode[HH]:MM

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

( 2016-04-29 00:12:35 +0200 )edit

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

( 2016-05-02 18:35:14 +0200 )edit

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.

more

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.

more

Sorry my bad, old school processor terms :https://en.wikibooks.org/wiki/Microprocessor_Design/FPU

( 2016-04-29 00:22:54 +0200 )edit

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"?

Sample:TimeFormat.png

more