Hrs & Mins format

[Spraggs]

Morning All,
In cell B3 I have minutes and A3 seconds.
This instance 108 (B3) and 56 (A3).
In cell C3 I want a formula and format that will result in Hr:Min:Sec (1:48:56)

Many Thanks Jase

 

[VoG]

Try

=A3/86400+B3/1440

Format as hh:mm:ss

 

[Spraggs]

This is returning "TRUE"
Where am I going wrong?

 

[VoG]

I'm not sure. It is working here

Excel Workbook
	A	B	C
3	56	108	01:48:56
Sheet3

 

[Spraggs]

Sorted.
Thanks VoG

 

[obiron]

or try

=FLOOR((A3*60)+B3,3600)/3600 & ":" & FLOOR(((A3*60)+B3) - FLOOR((A3*60)+B3,3600),60)/60 & ":" & MOD((A3*60)+B3,60)

format as General

This takes the minutes and multiplys by 60 and adds the seconds.
You then take the floor of that value / 3600 (3600 seconds in an hour) which gives you the hours as an integer. You then append a : and do the FLOOR of the remainder / 60 which gives you the minutes, append the : and then take the MOD of minutes which gives you the seconds.

This will work where the hours are > 24 and where the seconds are > than 60.

Which displaying a datetime as HH:mm:SS will not.

 

[VoG]

You need a Custom format of [h]:mm:ss for my formula to correctly display durations greater than 24 hours

Excel Workbook
	A	B	C
3	63	1508	25:09:03
Sheet3

 

[obiron]

good call VoG!

I have also noticed I got my minutes and Seconds columns mixed up!

Use VoGs method is you need to do further manipulation on the dates. use my method if you need to ensure that the data is output in that format (mine is a string representation of the time)