Differing date lengths - standardising format

[froggeja]

Hope someone can help. I've got over 100K dates that I need to format. I've search the data and there are 10 different lengths. I need to create a standardised formula that I can apply to the column and return a same length record.

I used the LEN function to establish length, however, when I used the formula =LEN(Text(cell,"mm/dd/yyyy")) it returns the lengths in brackets:
12/04/2012 00:00 = LEN is 5 (10)
02/04/2013 16:48 = LEN is 7 (10)
12/01/2012 13:12 = LEN is 8 (10)
12/04/2012 13:48 = LEN is 9 (10)
02/03/2013 09:18 = LEN is 10 (10)
01/04/2013 14:15 = LEN is 11 (10)
11/12/2012 15:20 = LEN is 16 (10)
2/28/2013 2:48:53 PM = LEN is 20 (20)
1/16/2013 10:48:50 AM = LEN is 21 (20)
12/17/2012 11:59:49 AM = LEN is 22 (22)

I also ran =CELL("format",A1). Those cells that had returned a (10) length were D1...and those that returned a (20+) were G.

I've tried using the format functions but haven't seemed to crack it.

If anyone can help, you'd be helping a very frustrated guy out!

Thanks in advance

[martindwilson]

date /times are actually numbers
12/04/2012 00:00 is 41011
02/04/2013 16:48 is 41366.7
11/12/2012 15:20 is 41254.6388888889
note thes values are what i see as they see these dates as d/m/y in my regional settings

thats why lengths are different dates start at 1 in 1/1/1900 and number up to today,the time is added on as a decimal one way will be to convert to a text date as you did

Text(a1,"mm/dd/yyyy")
or use
=INT(A1) that will give the day part without the time format cell mm/dd/yyyy

[bentleybob]

... or if you need the additional information

Please Login or Register  to view this content.

[froggeja]

Thanks for help so far. Have followed advice but unfortunately things aren't resolved:

I used: =TEXT(G9,"mm/dd/yyyy") and it returned the same data.
I used: =INT(G9) and it returned the same data

I then pressed CRL` which returned 2/8/2013 2:48:53 PM, whilst the dates that are D1 format (less than 20 characters in length) all return the serial number, but those that are 20+ characters in length and are G format all return the #VALUE! error

I tried to deconstruct the text placing Year in A1, Month in B1, Day in C1, Hour in D1 and so on. That didn't work.

Does anyone have any more suggestions? Your help is really appreciated.

[martindwilson]

2/28/2013 2:48:53 PM your excel wont recognise that as a date /time there is no 2nd of the 28th month
depending where/how you got your data

12/4/2013 may be wrong when put into excel in uk its seen as 12 april but US format its the 4th december
where is the data coming from?

[froggeja]

I'm ashamed to say that it's come from my customer database. However, the good news is that I think I've sorted it out using:

=(MID(CELL_ID,FIND("/",CELL_ID)+1,2)&"/"&LEFT(CELL_ID,FIND("/",CELL_ID)-1)&"/"&MID(CELL_ID,FIND(" ",CELL_ID)-4,4))+0

Thanks again for all comments and help.

[martindwilson]

the easiest way is before copying into excel is to change your regional settings to US,then put into excel. it will recognise everything correctly and give a valid date/time code
then change settings back to uk