How to calculate dates.(Microsoft Excel)
Here are some formulas to calculate the date of the next day, the day before, etc.
- 1. Steps
- 1.1. Formulas
- 1.1.1. Next day (N days later), previous day (N days before)
- 1.1.2. Next month (after N months), previous month (before N months)
- 1.1.3. Next year (N years later), Previous year (N years before)
- 1.1.4. Weekday
- 1.1.5. First day of the month
- 1.1.6. End of month (last day of the month)
- 1.1.7. Last business day.
- 1.1. Formulas
- 2. Links
Steps
Formulas
Next day (N days later), previous day (N days before)
To calculate future dates, add days to the date. (+2 means 2 days later)
To calculate a date in the past, subtract days from the date. (-2 means 2 days before)
Next month (after N months), previous month (before N months)
Use the YEAR, MONTH, DAY, and DATE functions.
=DATE(YEAR(datetime),MONTH(datetime) + Number of months ,DAY(datetime))
Adding the number of months results in N months later, and subtracting the number of months earlier results in N months earlier.
Normal calculation is performed even if the year-end and New Year holidays are crossed.
The EDATE function may be more convenient for months with different number of days.
Next year (N years later), Previous year (N years before)
Similar to the month calculation, but adds/subtracts to the result of the YEAR function.
=DATE(YEAR(datetime) + Number of years ,MONTH(datetime),DAY(datetime))
Weekday
How to display the day of the week automatically from the date.
First day of the month
Specifying 1 in the date portion of the DATE function sets the first day of the month.
=DATE(YEAR(datetime),MONTH(datetime),1)
End of month (last day of the month)
The first day of the following month is given and one day before the first day is calculated.
=DATE(YEAR(datetime),MONTH(datetime)+1,1)-1
Last business day.
How to get the last business day of the month.
---