Using Excel 2016, I want to enter 90 minutes as 90:00, or 75 minutes and 33 seconds as 75:33, or 102 minutes and 54 secs as 102:54; then sum the column to report as 268:27. How do I format the data in cells?
2 Answers
As far as I know, strictly speaking, what you’re asking for is impossible.
(Or possible but very hard.)
But you can get the effect you want by applying a custom format of [h]:mm
:
This is actually storing the times as
- 90 hours,
- 75 hours and 33 minutes,
- 102 hours and 54 minutes
but the math works out the same.
In format strings, h
(or hh
) means hours modulo 24,
which makes sense if you’re also displaying number of days (d
).
[h]
shows total hours, not modulo.
I don't think that there is any direct method but using little arrangements with Cell Format you can achieve it.
How it works:
- Select cell where you want to write time in Minutes.
- Apply this cell Format to cells:
[m]:ss
. - Enter time as usual for example for
90 Minutes
write01:30 (HH:MM),
for60 Minutes
2:00 (HH:MM)
and so on for others.
Note:
Just to confirm whether Excel is considering them in
Minutes
I've applies theSUM
, you can find the correct conversion inHours
inRed color
.Remember, to apply
[MM]:SS
format to the Formula cell also.