• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / 5 Awesome Date Functions in Excel

5 Awesome Date Functions in Excel

In this blog post, we explore five of the best date functions in Excel. There are many awesome date functions, so it is difficult to choose only five.

This includes date functions to calculate the number of working days between two dates, and automatically finding the date in exactly two months time.

Let’s dive into these awesome date functions in Excel..

TODAY

The TODAY function is used for a lot of good date calculation work. It returns the current date using your system clock.

This is essential for so much of what you may be trying to do in Excel. As the date changes every day, this function is used for getting your Excel features or formulas to roll with time, so that they work every day.

It is written as below;

=TODAY()

No information is needed by this function as it gets what it needs from your computer.

The video below shows the TODAY function being used to highlight dates older than 30 days.

NETWORKDAYS

The NETWORKDAYS function is used to calculate the number of working days between two dates. It is written as;

=NETWORKDAYS(Start Date, End Date, [Holidays])

The function will exclude Saturday and Sundays, as they will be considered weekend dates. There is a NETWORKDAYS.INTL function that is more flexible. This function allows you to specify which days of the week are worked.

Holidays is optional, and does not need to be supplied to the function. Holidays should be entered as a range of cells on your spreadsheet that contain additional non-working days to exclude from the result.

For example, the NETWORKDAYS function can be used to calculate the number of working days between the date an order was taken and when it was dispatched.

The following formula, returns the working days difference. Additional non-working days are referenced in range E2:E4.

=NETWORKDAYS(A2,B2,$E$2:$E$4)
Calculate working days difference with NETWORKDAYS

EDATE

The EDATE function returns the date a specified number of months before or after a start date. Its syntax is;

=EDATE(Start Date, Months) 

For example, the EDATE function can be used to calculate the end date of a contract. The formula below returns the date exactly 3 months from the dates in column C.

=EDATE(C2, 3)
EDATE function in Excel to calculate future dates

WORKDAY

The WORKDAY function returns a date a specified number of workdays before or after a start date. It is written as;

=WORKDAY(Start Date, Days, [Holidays])

This function can be used to calculate the expected finish date of a task or project, when given a start date and a number of working days duration.

The following formula, returns the date a specified number of working days (cell C2) from a start date. It excludes the optional Holidays argument.

=WORKDAY(B2,C2)
WORKDAY function to calculate expected end dates

One of the best date functions in Excel. It can also be used to return a date a specified number of workdays into the future, when using Conditional Formatting to alert you to upcoming due dates.

The WORKDAY function was also used in the Excel Gantt chart template of this blog.

There is also a WORKDAY.INTL function in Excel which is a much more flexible alternative to WORKDAY.

DATEDIF

The DATEDIF function is used to calculate the difference between two dates. The difference can be returned as years, months or days.

This function is not documented in Excel (weirdly?) so when entering it into a cell you will not get any information. However its syntax is;

=DATEDIF(Start Date, End Date, Interval)

The interval should be entered as a string so using double inverted commas. Use the first letter of the interval you wish to return e.g. “y” for years or “m” for months.

The interval can also be entered as a combination. So for example “ym” would calculate the number of months between the two dates excluding years. This returns a result as if the dates were in the same year.

The DATEDIF function could be used to calculate a person’s age. For example, the formula below will calculate the age of a person as of the current date, where cell B2 contains the person’s date of birth.

=DATEDIF(B2,TODAY(),”y”)

If you wanted to return the persons age as how many years and months old they are, we could use the formula below. The image shows the formula split over two lines for easier reading.

=DATEDIF(B2,TODAY(),"y")&" years "&DATEDIF(B2,TODAY(),"ym")&" months"

This formula uses the ampersand to concatenate two DATEDIF functions and some text.

DATEDIF used to calculate age in Excel.

Related Posts:

  • Entering Fractions in Excel
    Entering Fractions in Excel
  • Display Negative Time in Excel
    Display Negative Time in Excel
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel
  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel

Reader Interactions

Comments

  1. Rob Craft says

    17 April 2017 at 3:38 am

    Thanks for the tips in Excel. They are very easy to follow and well explained.
    Cheers
    Rob

    Reply
    • computergaga says

      17 April 2017 at 8:09 am

      Thanks very much Rob

      Reply
  2. Katherine says

    2 June 2017 at 3:52 pm

    Can the DATEDIF time interval be set to days or hours instead of months or years?

    Reply
    • computergaga says

      4 June 2017 at 8:07 am

      Hi Katherine,
      I don’t think so unfortunately. Here is a tutorial on how to find the difference between two times in days, hours and minutes.
      I hope that helps.
      Alan

      Reply
  3. Sujit Dhital says

    2 October 2017 at 1:27 am

    it will be better to me if you can give some VB tips

    Reply
    • computergaga says

      2 October 2017 at 4:51 am

      VB tips on the way Sujit.

      Reply
  4. Nasreen Khan says

    5 November 2017 at 4:58 pm

    Hi Alan
    your instructions are always easy to follow, and they have helped in so much in my place of work! thanks so much

    Reply
    • computergaga says

      6 November 2017 at 5:38 am

      You’re welcome Nasreen. Thank you.

      Reply
  5. JEWEL RANA says

    27 February 2018 at 10:31 am

    Nice Things

    Reply
    • computergaga says

      27 February 2018 at 3:34 pm

      Thank you Jewel Rana.

      Reply
  6. Linda Burrows says

    27 April 2021 at 7:49 pm

    I have several employees that have a few hire and rehire dates with our company. I know how to calculate the hire date to the term date, but how do you calculate if they were rehired to another term date? I only want the actual time worked with the company.

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·