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

Calculate first Saturday of month using reference cells for year and month values

Calculate first Saturday of following month using reference cells for year and month values

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

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.

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

To add one year use

Also see

• Excel Tips…Using TIME
• SharePoint Tips…Calculate date x days before Start Time
• Excel Tips…Using CONCATENATE with dates