serial value to date in excel

  • Thread starter Thread starter rbcochell
  • Start date Start date
R

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
 
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?
 
Hi,

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

Mike
 
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
Click to expand...

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)),"")))
 
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
 
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
Click to expand...

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)),"")))
 
Perhaps if we knew where these serial number dates were being generated from?
--
John C






- Show quoted text -
Click to expand...

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