Converting minutes to text "H:MM" format

[yshach]

I am storing a time counter in minutes (i.e 20, 75, 211, etc.).
In oredr to present it in a "H:MM" format, I have used the following:
=CONCATENATE(TEXT(INT(F7/60),"00"),":",TEXT(MOD(F7,60),"00"))

Could it be there's no elegant way/built in function to do this conversion?

Thanks in advance!

 

[barry houdini]

Your formula gives you a text result, to give a numeric result just use

=F7/1440

and format as [hh]:mm

or to give a text result

=TEXT(F7/1440,"[hh]:mm")

edit: .....or a 3rd option that would give you a numeric result also

=TIME(0,F7,0)

and format as hh:mm

note:this one only works when F7 is less than 1440

 

[yshach]

Sorry, missing input!

F7 is/can be bigger than 1440!

 

[parry]

Re: Sorry, missing input!

F7 is/can be bigger than 1440!

Did you actually try Barry's suggestion? Formatting as [HH]:MM and using F7/1440 will result in 25:00 if you had 1500 minutes.

 

[XLGibbs]

Perhaps he is misunderstand the purpose of the 1440, which is not explained to be 24 hours times 60 minutes.

By formating the resulting calculation as time, regardless of the number in F7, the formula will indeed work, because all values in excel are stored numerically such as the 5 digit serial number of a date or the decimal equivalent of a time, so excel does in fact know how to convert 1500/1440 into a time since that is the constant value for a specific time in "excel" anyway...

 

[yshach]

I did try the suggestion

It resulted in F7 mod 1440 (i.e. 70:30 hours shows as 22:30 hours). I must present it in hours:minutes (not days - its for a pilot logbook).

Can you please explain how did you format the cell to get the right answer? (I used Format->Cell->Number->Time->13:30).

Thanks, Yuval

 

[yshach]

OK, it works!

Tried the 2nd suggestion. Sorry for the trouble!

 

[parry]

Re: I did try the suggestion

It resulted in F7 mod 1440 (i.e. 70:30 hours shows as 22:30 hours). I must present it in hours:minutes (not days - its for a pilot logbook).

Can you please explain how did you format the cell to get the right answer? (I used Format->Cell->Number->Time->13:30).

Thanks, Yuval

Format|Cells|Custom then enter [HH]:MM

The square brackets around the hours ensures the hours increase beyond 24.

 

[barry houdini]

Sorry for the confusion

1st two suggestions I made should both work for you, I was only referring to the 3rd suggestion =TIME(0,F7,0) when I said it wouldn't work for values above 1440