Cell format date time duration

[StarMariner]

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.

[erAck]

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.

[StarMariner]

old habit, thanks .

[StarMariner]

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

[karolus]

Why not:

=A2+B2-A1-B1

with Formatcode[HH]:MM

[StarMariner]

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

[erAck]

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

[Morgan1]

What is the difference between [HH]:MM and HH:MM please? Thanks

[erAck]

[HH]:MM is a duration format where hours can be >23 and rounding is applied to the least significant part (here minutes). HH:MM is a clock time format that displays at most 23:59 and then wraps over to 00:00 and values are not rounded but truncated, e.g 23:59:59 is still displayed as 23:59 and not 00:00, like on a wall clock.

[StarMariner]

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.

[Lupp]

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|attachment](upload://7JCWzagPLpelg6slrYSbXCQVvXX.ods)er;1) respectively.

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

[StarMariner]

Sorry my bad, old school processor terms :Microprocessor Design/FPU - Wikibooks, open books for an open world

[Ratslinger]

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