Converting 2010 Julian dates to calender date

=("1/1/"&(IF(LEFT(S2,LEN(S2)-3)*1<20,2000,1900)+LEFT(S2,LEN(S2)-3)))+MOD(S2,1000)-1. I'm using this formula to convert a julian date (i.e. 9351) to a calender date (i.e. 12/15/09) and it works great for 2009 and earlier dates, but it won't work for 2010 julian dates (i.e. 0032) it gives me 2/01/00. Is there a way to fix this so it will recognize the date as 2010. Thanks you

Shouldn't the 2010 dates begin with 10?

1st Feb 2010 would be 10032 which works correctly with your formula.

See http://www.cpearson.com/excel/jdates.htm for an alternative formula and more info.

Again, you are right on the money, but I still have a problem. The database I copy from is already formatted to only show the julian date as a 4 digit number instead of the required 5 digit to accomodate the 2010 dates, and anything less than an act of God is not going to get it changed. Is there a formula I could plug in to my spreadsheet to add the "1" to the julian date? (ie showing "0032" converting to "10032". Thanks so much for all your help

My current date formula will work if I can figure out a way to add a "1" to the front of the document number (ie document # = 00320044 convert to 100320044). Is there a way to do that?

I was trying to give you a solution that would last for more than a year.....

You say you want to add a 1 but does than mean to all document numbers? do you still have 9321 type numbers that you need to convert to 2009 dates. If you add 1 to those you'll get the wrong dates, won't you?

What exactly is in S2 just the 4 digit Julian date or the whole document number?

I appreciate the help, maybe if I give you a little more info you can figure out an easier way for me to do this. Basically I'm using document number to determine the amount of days the part has been on order from today. First G2 has the document number (ie 00320014) where the 0032 is the julian date. In S2 I have the formula "=LEFT(G2,4)+0" which for 2009 document numbers (ie 9323033) worked perfectly and gave me "9323" but with document number 00320014 it gives me "32" If I change (G2,4)+0 to (G2,5)+0 and add a "1" to the front of 00320014 everything works great. Currently I then take the julian date and convert it to a calender date with a formula, then with another formula I subtract that date from todays date and it gives me the total amount of days since it was ordered. If you know an easier way please help. The only format that has to stay is the document number has to be in the G column, because of how it is pasted from the database.

Just working from G2 you could extract the date like this

=DATE(110+LEFT(G2),1,MID(G2,2,3))

that would give you 1st Feb 2010 for 00320014 and will treat all dates as if in the range 2010 to 2019. If you still want to recognise those that start with 9 as 2009 then change to

=DATE(100+LEFT(G2)+10*(LEFT(G2)+0<9),1,MID(G2,2,3))

If you want you can get the date difference from today in one go, i.e. for the first formula change to

=TODAY()-DATE(110+LEFT(G2),1,MID(G2,2,3))

You are the man!!!!