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..
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;
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.
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.
The EDATE function returns the date a specified number of months before or after a start date. Its syntax is;
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.
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.
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.
The DATEDIF function is used to compare two dates in Excel and return the difference in years, months or days.
This function is not documented in Excel due to known limitations with the “md” unit, so when entering it into a cell you will not get any information. However its syntax is;
=DATEDIF(start_date, end_date, unit)
The unit 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.
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.
Rob Craft says
Thanks for the tips in Excel. They are very easy to follow and well explained.
Thanks very much Rob
Can the DATEDIF time interval be set to days or hours instead of months or years?
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.
Sujit Dhital says
it will be better to me if you can give some VB tips
VB tips on the way Sujit.
Nasreen Khan says
your instructions are always easy to follow, and they have helped in so much in my place of work! thanks so much
You’re welcome Nasreen. Thank you.
JEWEL RANA says
Thank you Jewel Rana.
Linda Burrows says
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.