Working with the Date Format (including how to convert a date to the day of the week)

Do you know that Excel can perform calculations with dates?

When you key in a date, Excel actually stores the date as a number behind the scenes. This number is the number of days since 0 Jan 1900.

Let’s take a deeper look behind the curtains of Excel.

Summary

  • Taking a Peep Behind the Scenes
  • Subtracting Two Dates
  • Adding and Subtracting a Date and a Number
  • End Date After X Months
  • Last Date of the Month After X Months
  • Finding the Difference Between Two Dates
  • Finding the Number of Work Days Between Two Dates
  • Finding the End Date Given Start Date and Number of Work Days
  • Display the Day of Week

NO TIME TO READ?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

Taking a Peep Behind the Scenes

To see this number behind the scenes, you can highlight the cell with the date and press Ctrl + ` (the key beside 1).

1 Jan 1900 has the number 1 behind the scenes

Subtracting Two Dates

Since dates have a hidden number behind the scenes, this means that we can subtract two dates to find the number of days apart.

End Date – Start Date

Technically we can add two dates together since it’s the addition of two numbers, but it doesn’t make much sense.

Adding and Subtracting a Date and a Number

Next we can also find the Start Date or End Date using a date and a number.

Date + Days

End Date After X Months

Typically used in HR, say if you want to calculate when is the End Date of a 3-month probation. The EDATE function helps you to set future dates based on the number of months.

EDATE(date, months)

Last Date of the Month After X Months

In Accounting, we usually need to set up the last date of the month to present month-to-date figures. The EOMONTH function gets the last date of that particular month.

(tip: use month 0 for current month)

EOMONTH(date, months)

Finding the Difference Between Two Dates

To find the difference between two dates in days, we learn that we can simply subtract the two dates. But if you want to find the number of months or years between two dates, then the DATEDIF function will come in handy.

=DATEDIF(start_date, end_date, unit)

We have a few options for unit:

  • “y” – number of completed years
  • “m” – number of completed months
  • “d” – number of days
  • “ym” – after ignoring years, the number of completed months
  • “yd” – after ignoring years, the number of completed dates

If you are in HR, then perhaps you may need to calculate the age of an employee.

DATEDIF(start_date, end_date, unit)

Finding the Number of Work Days Between Two Dates

Another function used in HR is NETWORKDAYS.INTL, which is used to find the number of work days between two dates.

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

The format for weekend is a series of 7-digit number starting from Monday to Sunday, with 1 for not working and 0 for working. So for an employee not working on Saturdays and Sundays, their weekend format will be “0000011”.

For more examples, please see Microsoft’s article on NETWORKDAYS.INTL.

Finding the End Date Given Start Date and Number of Work Days

Similar to the previous NETWORKDAYS.INTL, the WORKDAY.INTL function works by finding the End Date given the Start Date and the number of work days.

=WORKDAY.INTL(date, days, [weekend], [holidays])

Its power shines in the HR industry where you can use a negative number of work days to count backwards from the date given.

Supposed we have an employee whose last day is 26 Dec 2018. He has 7 days of annual leave and the management has agreed for him to offset them against 26 Dec 2018.

So what’s his actual last day in the office?

WORKDAY.INTL(date, days, [weekend], [holidays])

Display the Day of Week

Sometimes we just want to display the day of the week instead of the date itself.

To do that, highlight the cell containing the date and go to Home | Number and click on the dropdown menu and select More Number Formats. Or you can simply press Ctrl + 1 as a keyboard shortcut.

Under Format Cells | Category | Custom | Type, in the box, type in:

ddd: for 3 letter abbreviations e.g. Mon, Tue, Wed
dddd: for the long form of the day e.g. Monday, Tuesday, Wednesday
Custom Number Format

Conclusion

Working with dates is one of the modules in my self-paced online course, DataCraft. If you like this article and is interested in mastering Excel to jumpstart your career, check out this premium course on data analysis — the fast track to Excel mastery.

NO TIME TO READ?

Grab the PDF version of this article to read it at a later time or print it out for your desktop reference.

Leave a Comment

Your email address will not be published. Required fields are marked *