issue with converting number to date need help!

Hello all, i have an issue with converting a type of ... julian date... to a regular date and my brain must simply not function today so i am requesting help.

i have a serial number: 09121673 for example

09 is the month 12 is the year 167 is the day and the last digit is ignored.

i am using this formula i found on here to do the date conversion: =("1/1/"&(IF(LEFT(A4,2)*1<20,2000,1900)+LEFT(A4,2)))+MOD(A4,1000)-1

it works but the month and year are switched i believe and again, i simply am having a "brain fart" moment today and cannot seem to figure it out!

thanks!!

Thanks for the reply! ... that gets the year correct but the month and day are still not working :-/

HI,
first of all I am considering the value "09121673". Since it is going to be a Julian date so month is insignificant. so our only consideration will be "12" and "167". So here is the formula which might be useful for you.

Please Login or Register  to view this content.

yes, i believe i goofed and explained it incorrectly...i was also recently told it is NOT an actual julian date *sigh* lol. it is simply serialized as month, year, day, so i altered it to this and simply inserted a "/"

=(MID(A1,1,2)&"/"&MID(A1,5,2)&"/"&MID(A1,3,2))

thanks for everyones help!