serial value to date in excel

[rbcochell]

This is what i have, i need to turn these values to a date in the
format mm/dd/yyyy please help, I cannot get this formula to save my
life.
A B
1 1001099 to mm/dd/yyyy
2 1001286 to mm/dd/yyyy
3 1024515 to mm/dd/yyyy
4 1041172 to mm/dd/yyyy

 

[John C]

Unless you are planning 2600 years in the future, what 'should' these serial
numbers be in date terms? i.e.: 1001099 I am assuming is NOT Nov 29, 4640, so
what should it be?

 

[Mike H]

Hi,

I may be being a bit slow here but in each of the examples what would you
like the date to be?

Mike

 

[rbcochell]

This is what i have, i need to turn these values to a date in the
format mm/dd/yyyy please help, I cannot get this formula to save my
life.
           A                B
1    1001099 to mm/dd/yyyy
2    1001286 to mm/dd/yyyy
3    1024515 to mm/dd/yyyy
4    1041172 to mm/dd/yyyy

I am going to reply to myself. I think this will solve the problem but
i need to play some more to verify.

=IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),""))

and

=IF(ISERROR(IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),""))),"invalid
date
format",IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),"")))

 

[Pete_UK]

It doesn't look like the 3rd one is a valid date. I presume you have
a1 tagged on to the beginning of a 6-digit date to preserve leading
zeros, so you then have mmddyy. You will need to account for the
century years.

Hope this helps.

Pete

 

[David Biddulph]

So you are saying that you are treating 1024515 as the 15th day of the
*45th* month of year 102, and calling that 15 Sep 2005, and that you're
treating 1001286 as the *86th* day of the 12th month of year 100, and
calling it 24 Feb 2001?

That seems a very strange way of expressing a date.
--
David Biddulph

This is what i have, i need to turn these values to a date in the
format mm/dd/yyyy please help, I cannot get this formula to save my
life.
A B
1 1001099 to mm/dd/yyyy
2 1001286 to mm/dd/yyyy
3 1024515 to mm/dd/yyyy
4 1041172 to mm/dd/yyyy

I am going to reply to myself. I think this will solve the problem but
i need to play some more to verify.

=IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),""))

and

=IF(ISERROR(IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),""))),"invalid
date
format",IF(LEN(TRIM(A1))=6,DATE(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2)),IF(LEN(TRIM(A1))=7,DATE(LEFT(A1,3),MID(A1,4,2),RIGHT(A1,2)),"")))

 

[John C]

Perhaps if we knew where these serial number dates were being generated from?

 

[rbcochell]

Perhaps if we knew where these serial number dates were being generated from?
--
John C






- Show quoted text -

yeah the data given to me was pooled incorrectly. sorry LOL and yes i
have no hair.