1

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:000 minutes

00:01:001 minute

00:02:002 minutes

01:00:001 hour

02:00:002 hours

03:01:003 hours, 1 minute

01:03:001 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)"

  • Edit the Title and it should be, "Format Time to display in Hour & Minutes". – Rajesh S Mar 19 '18 at 7:48
  • 1
    Nope. Why should I do that? – Ben Mar 19 '18 at 18:52
  • Because it's Cell Format or Format Date & Time. Conditional format is different. – Rajesh S Mar 20 '18 at 5:52
  • 1
    Well, that's only naming. In fact, it is a conditional number format. – Ben Mar 20 '18 at 21:38
1

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
  • I don't understand this line: 11:59:59 23 hours, 59 minutes – Ben Mar 19 '18 at 20:44
  • 1
    @Ben - sorry - it was a typo. I originally had the first column formatted with AM/PM then removed it but manually updated the example and didn't fix the last one. – andrew Mar 19 '18 at 23:30
  • 1
    @Ben - I've added an alternative to the answer - using a formula. – andrew Mar 20 '18 at 0:09
  • 1. Ah, I see. 2. Thank you very much! I appreciate that. – Ben Mar 20 '18 at 21:41
  • 1
    Yes you would use a different formula to calculate the hours and minutes from the total minutes. Ask an additional question if you want to go down that path. – andrew Mar 22 '18 at 2:41
1

Select the Time column and simply apply this Format, h" hours and "m" minutes". Your Date column will looks like this.

enter image description here

  • 1
    This is a pretty good answer. But the custom format should be h "hours, " m "minutes" to get close to what Ben is trying to do. As Andrew noted, it is likely not possible to have the labels be singular when the number is 1. – Bandersnatch Mar 19 '18 at 15:14
1

You could try using TEXT() in a formula with a lot of nested IF() 's. This would get very cumbersome in your case because you have so many possible formats. I counted 8 different possibilities.

As an example, this formula

=IF(HOUR(A1)=1,IF(MINUTE(A1)=1,TEXT(A1,"h"" hour, ""m ""minute"""),TEXT(A1,"h"" hour, ""m ""minutes""")),TEXT(A1,"h"" hours, ""m ""minutes"""))

does some of the formatting you want, as shown in the table below.

The format inside the TEXT() function has to be enclosed in single quotes, but the quotes surrounding the hours and minute labels must be "escaped" by using double quotes.

enter image description here

Also, I wasn't able to correctly display just the minutes. This formula gives "1 minute" for every value in column A:

=TEXT(A1,"m"" minute""")

1

You cannot do it with regular cell formatting for the reason that Excel will only take two of those conditional formats trhough its regular cell formatting. You have four variations.

So set regular cell formatting to the simplest one to write, as you are fairly limited in how you can express your conditions in regular formatting, but the hardest to test for in the Conditional Formatting rules. That would be for the 1 hour, 1 minute variation:

hh" hour, "mm" minute"

So without further work you'd have results like 23 hour, 14 minute. No worriies though since you are going to do further work.

Then fire up Conditional Formatting. Set a rule that tests for "singleness" in hour, but "pluralness" in minutes. Set the format to:

hh" hour, "mm" minutes"

(To be "precise" you could use just a single "h" as you know from the test it will be a single digit, 1 to be exact. But both work and the single "h" approach could mask failures in some aspect of the work.

Then the other similar variation, hour pluralness with minute singleness and format as:

hh" hours, "mm" minute"

And finally, neither having singleness and format as:

hh" hours, "mm" minutes"

You can test each element a variety of ways, but the cleanest, though not simplest, is to test each by converting via the TEXT() function so it is truly converted to what it seems to be as an output, testing the text result vs. literally "01" where you'd test against "plain" 1 if it were a number. Use the AND() function:

=AND(TEXT(A1,"HH")="01",TEXT(A1,"MM")<>"01")

(Note the HH and MM so as to not mask mistakes in the data or process causing things to go astray. The TEXT() function yields a true value, not one displaying only a portion of its content, and really being something else. So if the time was 3:34, it yields 03 for the hour with the 34 minutes completely gone, and 34 for the minutes with the 3 hours completely gone.

Reverse the comparisons for the second format, and make both comparisons "<>" for the last one.

Last element, but hideously important: make sure you check the box for "STOP if the condition is met!"

This seems trivial here, really, but ocnsider the approach vs. a very complicated set of concerns, say 8-10 categories for coloring text while wishing one could add fill coloring or use only fill coloring. You'd have a monster formula of IF this and IF that and so on. But this technique? Just 7-9 siomple rules to test for one after another (remember the first one is set in normal formatting). And which one would be easier to maintain? Add conditions to? Change conditions? For someone else to take over and quickly understand your work?

Yeah boy! This one!

Oh, and in that example, you don't have to do any of the testing in complicated nested IF's so the cell's basic formula can be "all business" — just the material needed for its basic calculation — because all the IF-ing needed for the formatting (and the forced results in some cases) is in the simple CF rules.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.