﻿﻿﻿﻿ DATE – tecivo BLOG

## tecivo BLOG

### My IT notes, 'How To...' guides, Microsoft, SharePoint, Citrix, AppSense, Apple, technology & gadgets and more!

#### Tag: DATE ## Introduction

If you want to include a date value in a cell using CONCATENATE to combine text and other cells, you need to include the TEXT function and include the format of the date you want.

A B C < C function >
1 Stephen 18/12/1946 Stephen’s birthday is 18/12/1946 =CONCATENATE(A1,”‘s birthday is “,TEXT,(B1,”dd/mm/yyyy”))
2 George 14 May 1944 George’s birthday is 14-May-1944 =CONCATENATE(A2,”‘s birthday is “,TEXT,(B2,”dd-mmm-yyyy”))
3 Christopher 30-7-70 Christopher’s birthday is 30 December 1970 =CONCATENATE(A3,”‘s birthday is “,TEXT,(B3,”dd mmmm yyyy”))

### Also see ## 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))))

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.

A B < B function >
1 June 2016 July 2016 =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))