Working With Dates 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.

This means that you can do maths with dates.  Subtract an earlier date from a later one to find the number of days between them.  Want to know the date two weeks hence?  Add 14 to today’s date.  But how do we find today’s date?  Here’s how:

TODAY

Enter =TODAY() into a cell with the equals sign (=) and the parentheses ( ), but no parameters.  Whenever the spreadsheet updates, that formula will give you today’s date.

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

DATE and DATEVALUE

Where you have the year, month, and day as numbers, you can enter dates using the DATE function.  Syntax:  DATE(year, month, day).  For example, DATE(2022,11,20) will give 20 November 2022.

Where you have text that looks like a date, you can enter dates using the DATEVALUE function.  Syntax:  DATEVALUE(date_text).  For example, DATEVALUE(“20 Nov 2022”) will give the date serial number for 20 November 2022.  Which date formats Excel recognizes will depend on your Windows Regional Settings.  Your machine might be set up to expect DATEVALUE(“Nov 20, 2022”) or something similar.

You can also just type the date into a cell, for example, 20 Nov 2022.  If the date changes to a different format, Excel recognized it as a date.  If not, you need to try a different format.  The year-month-day format yyyy-mm-dd is widely understood since it is unambiguous.  This is unlike dd-mm-yyyy (UK) and mm-dd-yyyy (US).

Formatting Dates with the Home Ribbon

To format a cell or range as a date, on the Home ribbon, in the “Number” group, pull down the list at the top.  Choose “Short Date” or “Long Date”.

This is also useful as a quick check of whether Excel accepted a date you entered, or treated it as text.  In the next example, look at the red-circled formats of Number, Currency, and Accounting.  All show the same formatting preview for text in cell A3 that my Excel does not interpret as a date:

On the other hand, in cell A4 I typed something that Excel saw as a date and then formatted in the dd-mmm-yy format.

Formatting Dates with the Format Cells Dialog

There was a politician who said, “those are my principles; if you don’t like them, I have others”.  In the same way, if the “Short Date” and “Long Date” formats do not suit you, Excel has 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 “Date”. In the “Type” list, you will see the top two formats marked with asterisks.  They are the “Short Date” and “Long Date” formats.  Below them are many others.

If none suit, select the Category “Custom”:

In the “Type” list, you can select an existing date format, or type your own 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?

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).

m           Single or double-digit month (Example: 4 or 11), or minutes if preceded by h or hh.
mm         Double-digit month (Example: 04 or 11), or minutes if preceded by h or hh.
mmm      Name of 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 Date as Text

Formatting a cell with a specific date format is all very well, but what if you want a date as part of a string?  A formula like =”The warranty expires on ” & end_date will not work!  You’ll get “The warranty expires on 45260”, which nobody will understand!

For this we need the TEXT function to format a number as text, thus:
=”The warranty expires on ” & TEXT(end_date, “ddd dd mmm yyyy.”)
This gives:
The warranty expires on Thu 30 Nov 2023.

The second parameter of the TEXT function is the format.  This uses, in quotes, exactly the date formats we learned about in the previous section.

How did we get the end-of-month, a year hence, for the warranty expiry?  Here’s how:

The Excel EOMONTH (End of Month) Function

EOMONTH(start_date, months) returns the last day of the month a specified number of months after a start_date.  Use EOMONTH to calculate due dates that fall on the last day of the month.  The function is not sophisticated enough to avoid weekends or holidays.

Example: =EOMONTH(TODAY(), 12) for the end-of-month a year hence.

Here is a post with more detail and examples on EOMONTH.

The Excel EDATE Function to Add Months

EDATE(start_date, months) returns the date a specified number of months after the start_date. Use EDATE to calculate due dates, like paydays, that fall on the same day of the month.  EDATE is not sophisticated enough to avoid weekends or holidays.

What if we do want to exclude weekends and holidays? Then we need NETWORKDAYS and/or WORKDAY:

Here is a post with more detail and examples on EDATE.

Work Days with NETWORKDAYS and WORKDAY

The NETWORKDAYS function in Microsoft Excel returns the number of whole working days inclusive from a start date to an end date. Working days exclude weekends and any dates listed as holidays. Use NETWORKDAYS to calculate pay or holiday benefits based on the number of days worked during a specific period.

The WORKDAY function in Excel complements NETWORKDAYS.  It calculates the date that is a given number of working days after or before the start date. As with NETWORKDAYS, working days exclude weekends and any dates listed as holidays.

NETWORKDAYS and WORKDAY treat weekends as the whole day Saturday and Sunday.  If your weekends are different, use the international versions, NETWORKDAYS.INTL and WORKDAY.INTL.  They let you specify your own weekends.

Syntax of NETWORKDAYS and WORKDAY

NETWORKDAYS(start_date, end_date, [holidays])

WORKDAY(start_date, days, [holidays])

Their arguments (almost identical) are:

• Start_date and end_date (required): The start and end date dates for the calculation. The start_date can be earlier than, the same as, or later than the end_date.  The latter will produce a negative answer.
• Days (required): The integer number of working days after start_date.  It excludes weekends and holidays. Use a negative value to get an earlier date.
• Holidays (optional): An optional range of dates to exclude from the working days.

Here is a post with more detail and examples on NETWORKDAYS and WORKDAY.

Other useful Date Functions:

DATEDIF(start_date, end_date, unit) calculates the number of days, months, or years between two dates. This function is useful in formulas where you need to calculate an age.  For Unit, use “Y” for the number of complete years in the period. Use “M” for the number of complete months in the period.

DAY(serial_number) returns the day number in the month, for a date serial number.

MONTH(serial_number) returns the month number in the year, for a date serial number.

YEAR(serial_number)  returns the year number of a date serial number.

WEEKDAY(serial_number) returns the number of the day in the week corresponding to a date serial number.  This is an integer from 1 (Sunday) to 7 (Saturday), by default.

Short Examples

To get the first of the month from any date in the month:
=start_date – DAY(start_date) + 1 or
=EOMONTH(start_date,-1) + 1

To get a specific day of the week from any date in the month, for example, the next Tuesday:
=start_date -WEEKDAY(start_date) +3 +IF(start_date -WEEKDAY(start_date) +3 < start_date, 7, 0)
How this works:
start_date – WEEKDAY(start_date) is always a Saturday.
Add 3 for a Tuesday.
However, that might be last Tuesday, so add 7 if it was.
Change both 3’s to 1 for Sunday, 2 for Monday, 4 for Wednesday, etc.  Remove both “+3” for Saturday.

In a later post, we will look at more complex examples.