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