Working With Times in Excel

Microsoft Excel stores dates as sequential serial numbers for use in calculations. 1 January 1900 is serial number 1. 2 January 1900 is serial number 2, and so on.  Thus, 1 January 2000 is serial number 36526 because it is 36,525 days after 1 January 1900.

Logically, a time of day is a fraction of a day.  Thus, 0.5 would be noon, and 0.25, 06:00.  You generally don’t need to calculate the fraction; Excel does it behind the scenes.

This means that you can do maths with times.  Subtract an earlier date-time from a later one to find the time between them.  Want to know the time two hours hence?  Add 2/24 to the time now.  But how do we find the time now?  Here’s how:

NOW()

Enter =NOW() into a cell with the equals sign (=) and the parentheses ( ), but no parameters.  Whenever the spreadsheet updates, that formula will give you the current date and time according to your computer’s clock.

How about a fixed time?  Two functions can supply that:

TIME()

Usually, you will not want a time in isolation, but rather a date with a time. We already know the DATE function to give you a date from the year, month, and day numbers.

The time function converts numbers for hour, minute, and second to a fractional date serial number.  Syntax:  TIME(hour, minute, second).  For example, =TIME(12,30,0) will give 12:30 PM (on “0 Jan 1900” unless you specify a date!).

TIMEVALUE()

Where you have text that looks like a time, you can enter times using the TIMEVALUE function.  Syntax:  TIMEVALUE(time_text).  For example, TIMEVALUE(“12:30”) will give the time serial number fraction for 12:30 PM (0.5).

You can also just type the time into a cell, for example, 12:30.  If the time appears right-aligned in the cell, Excel recognized it as a time.  If not, you need to try a different format.  Time formats in Excel aren’t as varied as date formats, but still depend on your Windows Regional Settings.  Your machine might be set up to expect a 24-hour clock, or am/pm.

Formatting Times with the Home Ribbon

To format a cell or range as a time, on the Home ribbon, in the “Number” group, pull down the list at the top.  Choose “Time”.  It will probably be a hh:mm:ss format, with or without am/pm.

Formatting Times with the Format Cells Dialog

If the “Time” format on the Home ribbon in the “Number” group does not suit you, you can have others.

Select the cells you want to format.  Then, either hit Ctrl+1 on the keyboard, or click the quick-launch button at the bottom right of the Home ribbon’s “Number” group:

The Format Cells Dialog will open:

On the “Number” tab, select the Category “Time”. In the “Type” list, you will see the top format marked with an asterisk.  That is the default “Time” format.  Below them are others.

If none suit, select the Category “Custom”:

In the “Type” list, you can select an existing time format, or a date-and-time format.

You can type your own format in the arrowed block at the top of the list.  Notice how the Sample changes as you choose or edit different formats.

What are those formatting characters?

h            Single or double-digit hour of the day (Example: 9 or 15).
hh           Double-digit hour of the day (Example: 09 or 15).
[h]          Number of hours (allows numbers 24 and above).

m           Single or double-digit minutes if preceded by h or hh, or followed by s or ss, otherwise month (Example: 4 or 11)
mm         Double-digit minutes or month (as for “m”, but always two digits).

s             Single or double-digit seconds (Example: 9 or 15).
ss           Double-digit seconds (Example: 09 or 15).

We would usually use “mm” (not “m”) and “ss” (not “s”).

For seconds with decimals, use “ss.00”.

There are also Date Formats:

d            Single or double-digit day of the month (Example: 9 or 21).
dd          Double-digit day of the month (Example: 09 or 21).
ddd        Name of the day of the week, 3 letters (Example: Wed, Thu).
dddd      Full Name of the day of the week (Example: Wednesday, Thursday).

mmm      Name of the month, 3 letters (Example: Apr, Nov).
mmmm   Full Name of the month (Example: April, November).

y, yy       Double-digit year (Example: 04, 22).
yyy, yyyy, e                Full year number (Example: 1904, 2022).

You can add punctuation as needed: spaces, dash (-), slash (/), comma (,) and other text.  Put literal text inside double quotes, thus: “My time is” hh:mm.  Without quotes, My time is hh:mm gives a result of 1122 ti122022 i28 09:12 or similar!

Displaying a Time as Text

Formatting a cell with a specific time format is all very well, but what if you want a time as part of a string?  A formula like =”This offer expires in ” & time_left will not work!  You’ll get “This offer expires in 2.36706863425934”, which nobody will understand!

For this we need the TEXT function to format a number as text, thus:
=”This offer expires in ” & TEXT(time_left, “d “”days”” hh:mm:ss!”)
This gives:
This offer expires in 2 days 08:52:49!

The second parameter of the TEXT function is the format.  This uses, in quotes, exactly the time formats we learned about in the previous section.  Using the cryptic “[h]” format to allow hours 24 and above, a better solution might be:

=”This offer expires in ” & TEXT(time_left, “[hh]:mm:ss!”)
This gives:
This offer expires in 56:52:49!

Other useful Time Functions:

HOUR(serial_number) returns the hour number in the day, for a time serial number.

MINUTE(serial_number) returns the minute number in the day, for a time serial number.

SECOND(serial_number) returns the second number in the day, for a time serial number.

Example of Working with Times: A Timesheet

The sample file Timesheet.xlsx is available from us.  It looks like this:

For the discussion on this example, please see this post.