2 Useful Excel Tips When Using HH:MM:SS Duration Format

So this weekend I was playing around in Excel trying to sort out our DVD library. I have a nice program that does most of it for me, but I wanted to do some other stuff, so Excel it was. Don't worry I'm not going to go into details about DVDs, but I did learn a couple of things that I just hadn't come across before.

I used to use Excel a lot when I was a consultant, but mostly I was doing hideously complex things in VBA, so a lot of the normal front end passed me by :).

2 Useful Excel Tips When Using HH:MM:SS Duration Format

The DVD program I'm using exports in hh:mm:ss format for the duration of the film. What I wanted was it all in minutes only. Hence some scrabbling around to find out how to make the data useful.

Displaying Hours, Mins and Seconds

We can use formatting to display just the hours, minutes or seconds.

hh:mm:ss	[h]	[m]	[s]
01:20:15	1	80	4815
01:40:15	1	100	6015
01:20:45	1	80	4845

• Highlight the cell or col with the hh:mm:ss in it
• Right Click and choose "format cell"
• Choose "cutsom"
• Type in [h] or [m] or [s] accordingly

The problem with this method is that the formatting converts the larger units into the chosen one ok, i.e. hours into minutes, hours and minutes into seconds but it ignores the smaller untis. There is not round up, only round down.

Hence if we need to take into account the smaller units we need another method.

Calculating Hours, Mins and Seconds

There is no nice function in Excel to calculate hours into mintes or into seconds, so we have to create a formula. Luckily there are funtions to extract the particualr parts so it is very easy.

A2 is the location of the cell where the hh:mm:ss value is stored.

hh:mm:ss to hours - =((HOUR(A2))+MINUTE(A2)/60+((SECOND(A2)/60))/60)

hh:mm:ss to mins - =((HOUR(A2)*60)+MINUTE(A2)+(SECOND(A2)/60))

hh:mm:ss to secs - =((HOUR(A2)*60*60)+MINUTE(A2)*60+(SECOND(A2)))

This gives us a decimal value. If we want a rounded up value so, for example, 01:40:15 converted so hours becomes 2, because that is the closest, then we can just use the format with no decimal places.

• Highlight the cell with the formula.
• Right click, choose format cell.
• Choose "number"
• Lower the number of decimal places to 0.

				Rounded Up
hh:mm:ss	hr	Min	Secs	hr	Min
01:20:15	1.34	80.25	4815	1	80
01:40:15	1.67	100.25	6015	2	100
01:20:45	1.35	80.75	4845	1	81

These are also numbers so they can be easily compared against other numbers.

As with all things in Excel, there are many ways of doing things, but these two were nice and straightforward and quick :). I also don't have the latest Excel, so for all I know there are now functions to do this anyway!