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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 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 |
| 1 |
June 2016 |
July 2016 |
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)) |
To add one year use
|
A |
B |
| 1 |
June 2016 |
June 2017 |
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) |
Also see
RT