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 semicolon – Ctrl; and press Enter
 It will populate today’s date. E.g. 090816
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 ddmmmyy. E.g. 09Aug16.
 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 cutoff 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 02Jan1900, the equal number will be 2. It means it is 2 days away from the system cutoff date, not including the cutoff 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 ddmmmyy 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 the EDATE function used?
By function, EDATE implies Expiry Date or Exact date from a given date.
Illustration 1: 14May2012 is the date of sale (Invoice Date). If the credit period is 30 days, then the due date of payment will be 14May2012 plus 30 i.e. 13Jun2012.
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 14Jun2012, and not 13Jun2012 as computed earlier. Given are two examples using 30 days vs. 1month 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. 25Feb12. Answer: 31Mar12.
Alternatively,
=EOMONTH (B3, 0) will give 29Feb12 [end of current month]
=EOMONTH (B3, 1) will give 31Jan12 [end of previous month]
=EOMONTH (B3, 0) + 5 will give 5Mar12 [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 22Jul1984.


 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. 22July1984.
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.
It’s often used along with IF logical statements.
4 – e) Important Excel date functions: TEXT()
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 22Jul84 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 30Dec11.
 Project duration: 5 business days
 Public Holiday: 2Jan12
 Weekend: Saturday and Sunday
 What is the Project Deadline? Ans: 9Jan12

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 30Dec11. It should take 5 business days to complete the project. Weekends (Saturday and Sunday) and Holidays (2Jan12) should be excluded. The picture above demonstrates how Day 1 – 5 is computed for arriving at the deadline date i.e. 9Jan12. 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 30Dec11.
 Project end date: 9Jan12
 Public Holiday: 2Jan12
 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 30Dec11 and ends on 9Jan12.
– Weekends (Saturday and Sunday) and Holidays (2Jan12) 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
Learn Advanced Excel Tutorial with ELearning Video Courses.
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 (17)
 Using TEXT() for dates
 Important Excel date functions for Project Management:
 WORKDAY.INTL
 NETWORKDAYS.INTL
Bonus Excel date functions: TODAY(), NOW()
Generally I don’t read post on blogs, but I would like to say that this writeup very forced me to try and do so! Your writing style has been amazed me. Thanks, very nice post.