In this article, let us learn more about Excel date functions and Excel date format. It will help you in areas of calculations such as due dates, project deadline & warranty expiry date.
Here is a summary of what we will explain:
How is a date stored in Excel (Hint: Every valid date is a number)
How to Insert today’s date in Excel using a Shortcut
How to change date format in Excel using a Shortcut
How to detect whether a date is a valid date (Hint: ISNUMBER)
How to change excel date format (Format Cells & Control Panel)
Important Excel date functions:
Where is EDATE function used?
Where is EOMONTH function used?
Other date functions: DAY(), MONTH(), YEAR(), DATE()
Using WEEKDAY (1-7)
Using TEXT() for dates
Important Excel date functions for Project Management:
WORKDAY.INTL
NETWORKDAYS.INTL
Bonus Excel date functions: TODAY(), NOW()
Table of Contents
What is Excel Date Functions?
How is a date stored in Excel (Hint: Every valid date is a number)
A data in Excel is not stored in Excel the way we see it. If you know how it is stored and read by Excel, it will help you write complex logical Excel date functions. It will also help perform date cleaning techniques.
Let’s perform a small experiment:
How to insert date in Excel using a Shortcut:
Go to a cell
Press the shortcut Ctrl semi-colon – Ctrl ; and press Enter
It will populate today’s date. E.g. 09-08-16
How to change date format in Excel using a Shortcut:
Now, choose the same cell and press Ctrl + Shift + 3. It will change the date format to dd-mmm-yy. E.g. 09-Aug-16.
Now, having seen the date, apply comma style from HOME tab on the cell. You will see it change into a number. E.g. 42,591.
The question is what does this number indicates and why is this relevant?
Every valid date is a number in Excel. It tells you how many days is the date far from the system defined cut-off date (31^{st} December 1899).
In the above example, the number equal of 9^{th} August 2016 is 42,591. It tells us that the date is 42,591 days away from 31^{st} December 1899. If a date in a cell isn’t getting converted to a number, it is not a valid date as per Excel date format.
Similarly, if you have a date of 02-Jan-1900, the equal number will be 2. It means it is 2 days away from the system cut-off date, not including the cut-off date.
_ _ _
How to detect whether a date is a valid date (Hint: ISNUMBER)
Since every valid date is a number, you can use a =ISNUMBER() function to check whether the date is a number. In the example below, TRUE indicates that a date is a number. FALSE indicates it’s not a number and hence, cannot be understood by Excel in its current form.Excel date format and Excel date functions will work correctly only if the date is valid and is understood by Excel.
How to change Excel date format (Format Cells & Control Panel)
If a date is valid, its format can be changed easily. You can do so in various ways:
Shortcut: Ctrl Shift 3 (transforms the cell’s date format to dd-mmm-yy format)
Shortcut: Ctrl 1 opens up FORMAT CELLS dialog box. It’s the same as FORMAT CELLS which you see upon right click of a cell
Control Panel Settings: You can also change the default Excel date format. It’s equally easy to customize the default date format for input. E.g. MDY, DMY etc. Once you open up Control Panel, click on Region Tab.
Under REGION (Region & Language) settings, you can change (1) COUNTRY. E.g. English (United States) or (2) you may go to ADDITIONAL SETTINGS. Refer picture below.You can change the date format to dd/mm/yyyy or dmy. Now if you write the date 22/08/2015 in a cell, Excel will accept it as a valid date.
Important Excel date functions: Where is EDATE function used?
By function, EDATE implies Expiry Date or Exact date from a given date.
Illustration 1: 14-May-2012 is the date of sale (Invoice Date). If the credit period is 30 days, then due date of payment will be 14-May-2012 plus 30 i.e. 13-Jun-2012.
However, if the terms of payment are one month from the date of sale (Invoice Date), then the due date will be found using EDATE. An answer should be 14-Jun-2012, and not 13-Jun-2012 as computed earlier. Given are two examples using 30 days vs. 1-month calculation.
See how the EDATE function is written:Illustration 2:
An employee will get a bonus upon completion of 5 years in the organisation. 5 years is equal to 60 months (5 yrs. x 12). When does he/she complete 5 years, if his / her date of joining is given?
Alternatively,
=EDATE (Date of Joining, 120) will give the date that is 10 years from the date of joining
Application areas of EDATE: Helps in the calculation of Due date, Deadline, Warranty period, Probation period, lease expiry, medicine or food expiry, Service Level Agreement (SLA) expiry date, financial modelling, completion of 5 yrs., 60 yrs. etc.
4 – b) Important Excel date functions: Where is EOMONTH function used?
EOMONTH implies End of Month date. If you want to see what will be the last date of next month of given date, then you need to apply EOMONTH formula.
In the example given below, =EOMONTH (B3, 1) will calculate the end of the month next to the date given in cell B3 i.e. 25-Feb-12. Answer: 31-Mar-12.
Alternatively,
=EOMONTH (B3, 0) will give 29-Feb-12 [end of current month]
=EOMONTH (B3, -1) will give 31-Jan-12 [end of previous month]
=EOMONTH (B3, 0) + 5 will give 5-Mar-12 [5^{th} of next month i.e. end of previous month plus 5 days]
Application areas of EOMONTH:
Tax payment due date & Financial modelling
Difference between EDATE and EOMONTH:
EDATE implies Expire date i.e. “x” no. of months from a given date
EOMONTH implies End of Month (current, next, previous etc.)
4 – c) Important Excel date functions: DAY(), MONTH(), YEAR(), DATE()
The three functions namely, DAY, MONTH and YEAR are often used in IF logical statements. They extract date information such day, month and year respectively, from a given date. See example below for 22-Jul-1984.
Day: 22
Month: 7
Year: 1984
DATE() function compiles a date from three numbers i.e. year, month and day – each needed in that sequence. Hence, =DATE(1984, 7, 22) will help build a valid date i.e. 22-July-1984.
Sometimes you get the day, month and year in separated adjoining columns. To get a single column of dates, we apply the excel DATE formula to combine them back in one cell. Once combined, Ctrl + Shift + 3 can be applied to change the date format.
4 – d) Important Excel date functions for Project Management: WEEKDAY
WEEKDAY functions tell you which day of the week it is. It gives you the numeric digit sequence of the day. 1 is Sunday and 7 is Saturday.
TEXT formula helps in the presentation of a data value in certain desired manner. It is the formula version of FORMAT CELLS. E.g. we wish to know if the date 22-Jul-84 was a Sunday or Monday. Will you open up the calendar and look for that day? No TEXT formula here helps.
This is how the format_text in the pair of double quotes work:
dddd – Sunday
ddd – Sun
dd – 22
mmmm – July
mmm – Jul
mm – 7
yyyy – 1984
yy – 84
dd/mm/yyyy – 22/07/1984
Application areas: Date analysis, Date representation (VAT returns and government filings)
5 – a) Important Excel date functions for Project Management: WORKDAY.INTL
WORKDAY.INTL date function helps calculate the project deadline. It needs project start date, project duration, weekends and holidays.
WORKDAY.INTL is the improvised version of the WORKDAY function and was introduced in version 2010. Difference: WORKDAY.INTL helps you define the weekends. It allows the user to specify which days are counted as weekends.
E.g. Weekends can be Sunday only, Friday and Saturday, Saturday and Sunday etc. It is useful to customise it as per different country’s norms.
E.g. Fri/Sat are weekends as followed by Saudi Arabia.
Case Study:
Project start date is 30-Dec-11.
Project duration: 5 business days
Public Holiday: 2-Jan-12
Weekend: Saturday and Sunday
What is the Project Deadline? Ans: 9-Jan-12
WORKDAY.INTL can help find the deadline date with the following inputs:
Start_date: Project start date (excluded from calculation)
Days: Project duration i.e. number of business days
Weekend: weekend type
Holidays: list of holiday dates
So how does WORKDAY.INTL function works?
The project starts on 30-Dec-11. It should take 5 business days to complete the project. Weekends (Saturday and Sunday) and Holidays (2-Jan-12) should be excluded. The picture above demonstrates how Day 1 – 5 is computed for arriving at the deadline date i.e. 9-Jan-12. This function excludes the start_date in its computation.
5 – b) Important Excel date functions for Project Management: NETWORKDAYS.INTL
NETWORKDAYS.INTL date function finds how many business days exist between two set of dates including both start and end date.
NETWORKDAYS.INTL is the improvised version of NETWEORKDAYS function and was introduced in version 2010. Difference: WORKDAY.INTL helps you define the weekends. It allows the user to specify which days are counted as weekends. E.g. Weekends can be Sunday only, Friday and Saturday, Saturday and Sunday etc.
It is useful to customise it as per different country’s norms. E.g. Fri/Sat are weekends as followed by Saudi Arabia.
Important Note: WORKDAY.INTL excludes the start_date in its computation whereas NETWORKDAYS.INTL includes the start_date.
Case Study:
Project start date is 30-Dec-11.
Project end date: 9-Jan-12
Public Holiday: 2-Jan-12
Weekend: Saturday and Sunday
How many business days it took to complete the project?
Ans: 6
NETWORKDAYS.INTL can help find the deadline date with the following inputs:
Start_date: Project start date (included in the calculation)
End_date: Project end date (included in the calculation)
Weekend: weekend type
Holidays: list of holiday dates
So how does NETWORKDAYS.INTL function calculates?
– The project starts on 30-Dec-11 and ends on 9-Jan-12.
– Weekends (Saturday and Sunday) and Holidays (2-Jan-12) should be excluded in calculating business days.
– The picture above demonstrates how Day 1 – 6 is computed for arriving at the answer.
I hope you like our blog, do share with your friend and colleague