# Converting minutes to text "H:MM" format

#### yshach

##### New Member
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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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

Sorry, missing input!

F7 is/can be bigger than 1440!

Re: Sorry, missing input!

yshach said:
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.

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...

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

OK, it works!

Tried the 2nd suggestion. Sorry for the trouble!

Re: I did try the suggestion

yshach said:
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.

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

Replies
2
Views
253
Replies
1
Views
381
Replies
5
Views
712
Replies
0
Views
431
Replies
14
Views
546

1,203,024
Messages
6,053,100
Members
444,639
Latest member
xRockox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back