tecivo BLOG

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

Tag: DATE

Excel Tips…Using CONCATENATE with dates

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”))
1 George 14 May 1944 George’s birthday is 14-May-1944 =CONCATENATE(A2,”‘s birthday is “,TEXT,(B2,”dd-mmm-yyyy”))
1 Christopher 30-7-70 Christopher’s birthday is 30 December 1970 =CONCATENATE(A3,”‘s birthday is “,TEXT,(B3,”dd mmmm yyyy”))

 

Also see

 

Excel Tips…Using DATE

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

 

© 2017 tecivo BLOG

Theme by Anders NorenUp ↑