A simple but very common task for many Microsoft Excel users.
Let’s assume that we need to calculate the delivery time of the goods, knowing the date of shipment and the duration. Since Excel actually stores dates as numbers (number of days since January 1, 1900), a simple addition will easily give us an estimated end date for delivery:
However, as you can see from the previous picture, there is no guarantee that the received date will not fall on a weekend when delivery is not made. Then you need to take the next working day, i.e. next Monday, but what is the best formula to do this?
The first thing that usually comes to mind is the construction with nested checks of the days of the week using the functions IF (IF) и DAY (WEEKDAY). Something like:
Those. if it’s on a saturdayDAY returned 6 for the delivery date), then we add 2 more days to move it to the next Monday. And if you hit Sunday, then add another day. Not the hardest formula.
In fact, you can manage much shorter and more elegant 🙂
Since 2007, Excel has a function WORKDAY (WORKDAY), which can shift the original date by a given number of working days, and the shift can be either positive (into the future) or negative (into the past). The trick is that if we take the previous day from the estimated delivery date and use this function to add one business day to it, we will get either the same date (if there were weekdays) or the next Monday (if the delivery fell on Saturday or Sunday ). What is required:
A nice bonus is the ability to specify a list of public holidays that the function WORKDAY will also be perceived as non-working except for Saturdays and Sundays. A range with holidays can be specified as a third argument:
It is easy to see that a similar approach can be used to search for the nearest previous rather than the next working day.
- How Excel actually stores and processes dates and times
- How to calculate age or seniority in Microsoft Excel
- How to determine how many given days of the week fall within a date range