Calculation of future data in Excel

The precise calculation of future data in Excel can create a significant time saving in everyday office work. Especially when it comes to incorporating special opening hours or holidays into the calculation, it is important to work with the right functions. In this tutorial, I will show you how to calculate a future delivery date taking into account special non-working days and holidays.

Key Insights

• The Excel function WORKDAY.INTL allows calculation of future data that apply only on specific workdays.
• You can include weekends and holidays in the calculation to achieve more accurate results.
• It is possible to include your own work schedule models in the calculation to increase flexibility.

Step-by-Step Guide

1. Understand Basics of Work Schedules

In the first step, you should be aware of the various work schedule models. Some companies are closed not only on Saturdays and Sundays but also have non-working days on weekdays. This is especially the case in small companies where part-time employees are employed.

2. Define Delivery Times

If you want to adjust the delivery times, it must be clear on which days employees are available. For example, if a company is only open from Tuesday to Thursday, this affects the delivery time.

3. Use the WORKDAY.INTL Function

Now is the time to use the Excel WORKDAY.INTL function. This function allows you to define the days when work is performed. You can set the start date – in this case, for example, today's date – and the number of days to be added.

4. Define Special Weekend Days

With the WORKDAY.INTL function, you can now also specify specific weekend days. In one parameter, you specify which days you want to consider as weekends. By default, these are Saturday and Sunday, but you can choose other combinations as well.

5. Include Holidays

In addition to weekends, you can also include holidays in a further step. To do this, list the holidays on a separate datasheet and then add them to the equation.

6. Perform an Exemplary Calculation

Now you can perform an exemplary calculation. For example, if you want to set the delivery date to September 4, 2018, you can ensure that this date is correct by following the previous steps, taking into account both holidays and special opening hours.

7. Verify the Result

After entering the relevant data, you should verify the final result. Make sure that all days that are not considered workdays have been correctly excluded.

8. Define Flexible Work Models

For added flexibility, you can also define your own work models. This is done by using one and zero in the function, where one represents "active" and zero represents "closed." This way, you can directly specify which day should be excluded from the calculation.

9. Check the Impact on the Delivery Date

After correctly setting all parameters, check the impact on the delivery date. For example, if you work 3 days less, the original date will shift accordingly.

10. Conclusion

Proper handling of the function is crucial for calculating precise delivery dates. Be sure to carefully set all parameters to guarantee accurate results. If you have any questions, feel free to ask!

Summary

In this guide, you've learned how to use the WORKDAY.INTL function to calculate a future date in Excel taking into account weekends, holidays, and special work schedules. This knowledge will help you work more efficiently in the office and ensure timely deliveries.