• Home  / 
  • Excel
  •  /  Working with Excel Date Functions

Working with Excel Date Functions

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:

1 – How is a date stored in Excel (Hint: Every valid date is a number)
     a. How to Insert today’s date in Excel using a Shortcut
     b. How to change date format in Excel using a Shortcut

2 – How to detect whether a date is a valid date (Hint: ISNUMBER)

3 –
How to change excel date format (Format Cells & Control Panel)

4 – Important
Excel date functions:

      a. Where is EDATE function used?
      b.Where is EOMONTH function used?

5 – Other date functions: DAY(), MONTH(), YEAR(), DATE()

      a. Using WEEKDAY (1-7)
      b. Using TEXT() for dates

6 – Important Excel date functions for Project Management:
      a. WORKDAY.INTL
      b. NETWORKDAYS.INTL

7- Bonus Excel date functions: TODAY(), NOW()

Excel Date Functions

Let’s start with Excel Date Functions:

1) 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:

a) 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

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

Change the date format and apply comma style particular cell - Excel Date Function
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 (31st December 1899).

In the above example, the number equal of 9th August 2016 is 42,591. It tells us that the date is 42,591 days away from 31st 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.

Every valid dates excel stored as number - Excel Date Function

_ _ _


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

Using =ISNUMBER() function Excel date format and Excel date functions will work correctly only if the date is valid and is understood by Excel.Excel date format and Excel date functions will work correctly only if the date is valid and is understood by Excel.

_ _ _ _ _ _

3) 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:

  1. Shortcut: Ctrl Shift 3 (transforms the cell’s date format to dd-mmm-yy format)
  2. 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
    Change the Date format functions with help of Shortcuts in MS Excel
  3. Control Panel Settings: You can also change the default Excel date format. It’s equally easy to customise the default date format for input. E.g. MDY, DMY etc. Once you open up Control Panel, click on Region Tab.

Control Panel settings Change default excel date format function

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.
Under REGION settings, you can change (1) COUNTRY or (2) you may go to ADDITIONAL SETTINGS. - Excel date function

In the Additional settings change the date format dd/mm/yyyy while using excel date function

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.

_ _ _ _ _ _

4 – a) 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.

By Excel date function, EDATE implies Expiry Date or Exact date from a given date.

See how the EDATE function is written:

Example of the Edate(terms of payment) - Excel Date Function
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?

Example of the Edate(Employee Bonus) - Excel Date Function

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.

EOMONTH implies end of month date. Want to see last date of next month of given date apply EOMONTH - Excel date function

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 [5th 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

Extract date information such day, month and year respectively from a given date - Excel Date Function

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.

Date function complies three numbers i.e. year, month, and day

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.

Excel Date Function - weekday functions tells which day of the week it is

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 22-Jul-84 was a Sunday or Monday. Will you open up the calendar and look for that day? No TEXT formula here helps.

Excel Date Function - Text formula helps in presentation of data value in certain desired manner

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.

Excel Date Function - WORKDAY.INTL date function helps calculate the project deadline

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 date function Excludes Start date in excel

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 date function include Start date in Excel

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

Learn Advanced Excel Tutorial with E-Learning Video Courses.

Comment below regarding Userform

About the author

Rishabh Pugalia and Niladri Sekhar Biswas

Author CA. Rishabh Pugalia has 10+ yrs. of work experience in Audit, Interest Rates Trading & I-Banking & Author Niladri Sekhar Biswas has 9 years of experience working in Microsoft Excel, as Excel and VBA corporate trainer

  • I’ve been surfing on-line more than 3 hours nowadays, yet I by no means
    discovered any fascinating article like yours. It’s beautiful price enough for me.
    In my view, if all site owners and bloggers made excellent content material as you did, the web can be a lot more useful than ever before.