Introduction
If you want to do calculations based on date cells you need to use the DATE, WEEKDAY, YEAR and MONTH functions.
Calculate second Tuesday of month
=DATE(year,month,(2*7+1)-WEEKDAY(DATE(year,month,8-DAY(dayoftheweek))))
dayoftheweek Sunday=1, Monday=2, etc
e.g. second Tuesday of June 2016
A | < A function > | |
---|---|---|
1 | 14/06/2016 | =DATE(2016,6,(2*7+1)-WEEKDAY(DATE(2016,6,8-DAY(3)))) |
Calculate first Saturday of month using reference cells for year and month values
A | B | C | < C function > | |
---|---|---|---|---|
1 | 2016 | 06 | 04/06/2016 | =DATE(A1,B1,(1*7+1)-WEEKDAY(DATE(A1,B1,8-DAY(7)))) |
Calculate first Saturday of following month using reference cells for year and month values
A | B | C | < C function > | |
---|---|---|---|---|
1 | 2016 | 06 | 02/07/2016 | =DATE(A1,B1+1,(1*7+1)-WEEKDAY(DATE(A1,B1+1,8-DAY(7)))) |
Adding days, months, years
When you have a date in cell and in another reference it to add 1, Excel will by default add one day
A | B | < B function > | |
---|---|---|---|
1 | 01/06/2016 | 02/06/2016 | =A1+1 |
Because the cell number format determines how the cells actual value is presented and not the value of the cell itself, adding +1 to a cell containing simply month and year wouldn’t return following month like you might expect.
A | B | < B function > | |
---|---|---|---|
1 | June 2016 | June 2016 | =A1+1 |
Excel stores cell A1 as 01/06/2016 even though the cell number format applied is mmmm yyyy and therefore A1+1 = 02/06/2016 which is being displayed with same number format rule as June 2016.
To add one month use
A | B | < B function > | |
---|---|---|---|
1 | June 2016 | July 2016 | =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) |
To add one year use
A | B | < B function > | |
---|---|---|---|
1 | June 2016 | June 2017 | =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) |
Also see
- Excel Tips…Using TIME
- SharePoint Tips…Calculate date x days before Start Time
- Excel Tips…Using CONCATENATE with dates
Leave a Reply