serial value to date in excel
 

On Aug 1, 10:07*am, John C <johnc@stateofdenial wrote:
Perhaps if we knew where these serial number dates were being generated from?
--
John C



" wrote:
On Aug 1, 9:15 am, wrote:
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),RI GHT(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(LEF T(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)),"")))- Hide quoted text -

- Show quoted text -

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