Skills Level: Intermediate
I have done a post before explaining the basics of dates and how Excel’s referencing system allows you to make calculations with dates. If you missed the post, you can read it here.
This blog post takes it a step further, and will illustrate a few very handy formulas to determine the last day of the month, when the next working day is, what day of the week a certain date falls and so on.
Do you know which day of the week you were born on? Was it a Monday, Friday or Sunday? Try out this formula if your mom hasn’t told you, or if you never thought of checking before. The weekday formula will determine which day of the week a specific date falls on by giving it a number between 1 and 7 which relates to a day of the week.
Suppose your date of birth is 4 September 1984.
The serial_number argument refers to the date that you want to determine the day of week for (4/9/1984).
The [return_type] argument allows you to specify whether you want day 1 to be a Monday or a Sunday (and some other alternatives).
I am going to select the second option which allocates the number 1 to Mondays all the way up to 7 for Sundays. The result returned is the number 3, which in this case means the 4th of September 1985 was a Wednesday.
The EDATE function is used to add (or subtract) any number of months to a start date. It can be useful when you need to draw up a table with dates at certain intervals (eg, the 7 of every month, or the 15th of every 2nd month) or determining expiry dates (which will fall on same day as the issue date of your driver’s licence for example).
If you need to determine a date, which is a specified number of months BEFORE the start date used within the formula, you will simply use a negative number of months in the “months” argument in the formula.
The end of month function returns the last day of a month (28th, 30th, 31st or 29th in the case of a leap year) based on a specified start date and number of months after, or before the start date. For example, you can use it to determine the payment date of an invoice that you receive this month, but only need to pay on the last day of the next month.
Suppose your invoice date is 7/10/2017, and you need to pay it on the last day of next month to avoid paying interest. The start-date in your formula will be 7/10/2017, and number of months will be 1 month (if you use zero in the “months” argument, it will return the last day of the same month). The result is 30 Nov 2017.
DATEDIF(start_date, end_date, unit)
The “date difference” function is actually not supported by Microsoft. It is therefore not easily found (the useful syntax which normally appears as soon as you start typing a formula, will NOT appear when you start typing out this formula) and you will have to remember the arguments contained in it. It is however suitable (and very useful) for simple calculations.
It calculates the difference between 2 specified dates, and returns the result in either days (“d”), months (“m”), or years (“y”). Note however, that it returns only FULL days, months or years. It therefore does not make use of decimals and basically rounds the result DOWN to a whole number.
To illustrate, let’s assume you are studying towards a 3-year bachelor’s degree which you expect to complete by 30/11/2019. You are getting anxious and want to know how many days, months or years respectively you still have to go.
Use the same start date and end date in each formula, and alternate the unit by changing the last argument to “d”, “m” or “y” in turn to see how it calculates each. The result is 784 days to go, or 25 months or 2 years.
WORKDAY(start_date, days, [holidays])
The workday function returns the date of the working day a specified number of working days before or after a start date, and takes into account any other holidays specified.
For example, suppose a project takes 15 working days to complete. If it is started on the 3rd of April 2017 and the 14th, 17th and 27th of April are holidays, the project will be completed by 26/4/2017.
Below is an image of the calender month, just to see how the formula gets to the result. The first day after the start day (4th of April) is calculated as the first of the 15 days required for the project, and therefore the 26th will be the day that the project will be completed.
Note that the Holidays argument is optional. If you do not wish to specify any holidays, this argument can be omitted from the formula. If so, Excel only uses Saturdays or Sundays as non-working days.
If you want Excel to use weekend days other than the default Saturdays and Sundays, then the WORKDAY.INTL function can be used instead where an argument is included to specify which days to use as weekends:
NETWORKDAYS(start_date, end_date, [holidays])
Suppose that you have a start date and end date, and want to know how many working days there are between the 2 dates (like a countdown of workdays until you can leave for holiday!) The networkday function does just that.
Let’s use the same month of April to determine how many working days there are in the month. The start date is the 1st of April, and the end date is the 30th, and once again, 3 public holidays need to be accounted for. The result is 17 working days in April 2017.
NOTE: The calculation includes both the start date and the end date as full days.
Once again, there is an option to use weekend days other than Saturday and Sunday. If this is your requirement, make use of the NETWORKDAY.INTL function instead which allows you to select which days need to be accounted for as weekend days.
To test yourself on what you have learned here, download the Excel Challenge below and see how well you can solve the tasks. The solution is available on the Challenges Page.
Hi, and welcome to my blog :) I am an Excel enthusiast and want to help others keen on improving their own Excel skills. I hope you learn something useful on my blog.