Microsoft Excel – Conditional Number Formatting Guide

conditional formattingmicrosoft excel

There is a column with time periods in the hh:mm:ss format. The seconds aren't used. The time period of three hours and two minutes would be 03:02:00. How to conditionally format the cells to get the following results?

00:00:00 → 0 minutes

00:01:00 → 1 minute

00:02:00 → 2 minutes

01:00:00 → 1 hour

02:00:00 → 2 hours

03:01:00 → 3 hours, 1 minute

01:03:00 → 1 hour, 3 minutes

I unsuccessfully tried custom format strings like:

[<60]hh:mm:ss "minutes";[<120]hh:mm:ss "minute";[<3600]hh:mm:ss "minutes";hh "hour(s)," mm "minute(s)"

Best Answer

I don't know of any "standard" date/time format built-in to nicely format in this way, so yes your attempt at a custom format seems appropriate.

However, the reason it is not working for you is that time values are not expressed in seconds like you assume - they are stored in a decimal value where 1 is 24 hours.

So you have to use the decimal values equivalent to your desired breakpoints. Unfortunately, it seems Excel limits the number of breakpoints to three, so I can't get additional breakpoints for "1 minute" (singular), etc. Even if there were more breakpoints, you could never achieve "N hours" because you would need infinite breakpoints (there's no condition that detects a zero minutes component).

So the closest I can get with a custom format is:

[<0.000694]"0 minutes";[<0.041666][m] "minutes";h "hours", m "minutes"

Which gives the following:

00:00:00    0 minutes
00:01:00    1 minutes
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hours, 0 minutes
01:01:00    1 hours, 1 minutes
02:00:00    2 hours, 0 minutes
02:01:00    2 hours, 1 minutes
01:03:00    1 hours, 3 minutes
23:59:59    23 hours, 59 minutes

I would probably instead suggest writing a VBA function and formatting the field in the formula.

But also, I would suggest you consider whether the value should be a time in the first place. Storing an elapsed period would be better in a specific unit (such as minutes in your case). You still couldn't have infinite breakpoints, but you avoid an obvious problem with the above - it rolls over after 23 hours, 59 minutes.

EDIT: For fun I made a formula which produces your desired results. It's not a format as your question asks for but because that didn't give the desired result exactly, I figured this might be a good alternative:

=if(hour(a1)=0,if(MINUTE(A1)=0,"0 minutes",if(minute(a1)=1,"1 minute",minute(a1) & " minutes")),if(hour(a1)=1,if(MINUTE(A1)=0,"1 hour",if(minute(a1)=1,"1 hour, 1 minute","1 hour, " & minute(a1) & " minutes")),if(MINUTE(A1)=0,hour(A1) & " hours",if(minute(a1)=1,hour(A1) & " hours, 1 minute",hour(A1) & " hours, " & minute(a1) & " minutes"))))

00:00:00    0 minutes
00:01:00    1 minute
00:02:00    2 minutes
00:03:00    3 minutes
01:00:00    1 hour
01:01:00    1 hour, 1 minute
02:00:00    2 hours
02:01:00    2 hours, 1 minute
01:03:00    1 hour, 3 minutes
23:59:59    23 hours, 59 minutes
Related Question