Do you want to advance your skills by learning Excel? Performing date calculations can sometimes be troublesome. Trying to calculate the number of working days between two dates, or automatically finding the date in two months’ time is not as straight forward as formulas with general numbering can be.
Fortunately Excel has a full repertoire of fantastic date functions. Here are five of the best.
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 the technique for getting your Excel features or formulas to keep track of it 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])
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 contains the holidays.
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 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 probation period of an employee. The formula below returns the end date of a 3 month probation period.
Video of EDATE and EOMONTH
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])
For example, this function has been used in my Gantt Chart template. It calculates the finish date of a task given a start date (D7) and a tasks duration (C7). Holidays have been provided by using the nonworking range name.
Find out about the Excel Gantt chart template.
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.
If you wanted to return the persons age as how many years and months old they are we could use the formula below.
=DATEDIF(B2,TODAY(),”y”)&” years “&DATEDIF(B2,TODAY(),”ym”)&” months”
This formula uses the ampersand to concatenate two DATEDIF functions and some text.
Learning Excel is easy with Computergaga – browse our website to find out more