How to use a conditional operator in a custom number format for dates and times in Excel?

Question

How to use a conditional operator in a custom number format for dates and times in Excel?

If I have a column of numbers, I can use a custom number format to display a singular or plural word.

Right click cell > Format Cells... > Number > Category: Custom > Type:

[=1]# "item";# "items"

> OK

For a cell containing 1, the custom number format displays "1 item".

For a cell containing 7, the custom number format displays "7 items".

Likewise, I want to do the same with time.

If I have a column of times, such as 00:01:00, 00:20:00, 00:25:00, etc., I want to display "1 minute", "20 minutes", "25 minutes", etc.

I first tried [=1][m] "minute";[m] "minutes", but it turns out that 1 means 24 hours or 1 day.

The value of 1 minute is

1 / 24 / 60 = 1 / 1440 = 0.0006944444444444444444444444...

I tried entering a fraction, but the [=1/1440] in [=1/1440][m] "minute";[m] "minutes" turns into [=1].

Unfortunately, I tried [=0.000694444444444444][m] "minute";[m] "minutes", but that does not work.

The following works, but I want to use an exact minute value.

[<=0.000694444444444445][m] "minute";[m] "minutes"

Is there a format for 1 minute that works?

---

Here is the documentation on the custom number format:

• Create or delete a custom number format:

  http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HA102749035.aspx

Answer 1

Interesting question. At first sight, trying this fraction format:

[=0 1/1440][m] "minute";[m] "minutes"

seems to work. After entering it you will see, however, that this displays the same decimal as in the post. This is ok for the current session, but saving the file and reopening reverts to the original problem again.

The issue is that values in number formats are rounded to 15 digit precision, whereas 17 digits are needed to specify the internal floating points numbers exactly. One way to see this is to try this 17 digit precision test from the immediate window:

?[0 1/1440]=0.00069444444444444444

This returns True but removing the last two 4s so there are only 15 digits of precison returns False.

Given these observations, I think the only reliable way is to enter the 15 digit interval containing the decimal value equivalent to one minute. This can be done using the following number format:

[>0.000694444444444445][m] "minutes";[>0.000694444444444444][m] "minute";[m] "minutes"

(or if seconds were included the first value could be replace by the 2 minute value i.e. 0.00138888888888888)

An alternative method is to use conditional formatting which would probably be simpler. First apply the default number format [m] "minutes" to the cell. Then from the ribbon select Conditional Formatting > New Rule > Use a Formula... with the options: Formula...: =MINUTE(A1)=1 NumberFormat: [m] "minute"