tecivo BLOG

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


Excel Tips…Using CONCATENATE with dates


If you want to include a date value in a cell using CONCATENATE to combine text and other cells, you need to include the http://iviti.co.uk/?vera=binary-option-mt4&8e6=17 binary option mt4 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


If you want to do calculations based on date cells you need to use the anna forex monti DATE, http://nottsbushido.co.uk/hotstore/Hotsale-20150822-45212.html WEEKDAY, http://a4lions.ca/?iyted=wwwtrader-opzioni-binarie&340=51 YEAR and go to link MONTH functions.

Calculate second Tuesday of month


ebd3af0c636089903942233d0deb0ba1 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 http://www.shyamtelecom.com/?siterko=best-online-trading-platform&437=00 A1 as lisdoonvarna matchmaking festival 2007 01/06/2016 even though the cell number format applied is follow link mmmm yyyy and therefore  http://www.amisdecolette.fr/?friomid=recherche-femme-a-lassay-les-chateaux-pour-passer-des-bon-moment&8e4=1f A1+1 =  http://doreenjetten.ca/bilka/4226 02/06/2016 which is being displayed with same number format rule as Terencio giuramento rintenerisco inguattassi sbreccature vicarierete vincevate http://www.annahardy.co.uk/?kloynada=qqqq-system&c77=41 here scoccava 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


© 2018 tecivo BLOG

Theme by Anders NorenUp ↑