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.

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.

1. Behind the scenes

beind-the-scene

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

date-general-format

2. 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
date-subtract

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

=start_date + days
date-add

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

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

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

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


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

9. 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
format-cells

Conclusion

Working with dates is one of the modules in our Excel workshops. If you like this article and is interested in mastering Excel to advace your career, check out our Excel courses.

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.

Get The Defeat Excel Handbook Today!

50+ Frequently asked how-tos

No more struggling with how to ask an Excel question. This book is written exactly the way you asked questions.

PDF Format

Easily search through the book to find the solution you need. Plus it works offline too.

Effective solutions

No need to read through a few thousand words or long videos. Each solution is carefully chosen to be short, direct and effective.