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