This article presents all cases to calculate automatically a date from another date.
All formulas are based for example on the current date TODAY () but of course all formulas work with any other date.
Table of Contents
Number of days in a month
Excel allows you to calculate number of days in a month. Here you have different techniques of calculation.
First and last day of a month
Excel also allows you to find any first and last day of any month. It's very interesting for the establishment of a report that automatically updates each month.
Calculation of the week number
There is a difference in calculations of numbers of weeks between North America and the rest of the world. And as the WEEKNUM function was developed by Microsoft (USA), the function returns the number of weeks depending on the method of calculating U.S.
First / last day
The list below shows all calculations necessary to return the first Monday of a month, a year or so on the last Monday of a month and the last workday of the year.
Legal holidays Variables
Easter day determines the other legal holidays of the Christian calendar (Ascension, Whitsun). The following Excel's formula allow you to calculate every different legal holidays for any year. You can also find the formula for Thanksgiving.
Calculation of the first business day for all months
In many situations, the planning of a project set up meetings every day of the month. For example, each of 4, you gather your team for a review on the situation. But some months, 4 could be a Saturday or Sunday. In this case, you must shift your meetings to fall on a business day. The following formula allows you to make these calculations.
Change the number 4 to see the result directly in the spreadsheet!
Shift of one or more days in a week
When you work in a week, you want to create workbooks that are able to refresh and display the previous Monday, the current Monday and also the next one. All formulas on the calculation of weeks are listed below.