PDA

View Full Version : [SOLVED] Setting the date as dd/mm/yyyy from a combined field



heathb
02-08-2017, 06:16 AM
Hi all..

Am having a lot of trouble trying to format a field containing the following (as an example "2014-09-01T13:58:04Z" ) into a date.
A couple of examples below. I always get the date "01/10/2014" however excel does not recognise this as a date when filtering etc. I have about 50k rows to update and would rather not do this manually.


All help appreciated








=RIGHT(LEFT(CY5,10),2)&"/"&RIGHT(LEFT(CY5,7),2)&"/"&LEFT(CY5,4)[/TD]


[CODE]=TEXT(LEFT(CY7,10),"dd/mm/yyyy")[CODE]
thanks

GTO
02-08-2017, 06:34 AM
If the entries are consistently like: 2014-09-01T13:58:04Z

Maybe like: =DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2))

Hope that helps,

Mark

heathb
02-08-2017, 06:40 AM
perfect, many thanks Mark

GTO
02-08-2017, 07:20 AM
You are most welcome:)