Convert Julian date/ time to regular date/ time (MM/DD/YYYY HH:MM:SS)

Hi everyone, new guy here...

I am trying to convert a Julian date and time to a normal date and time, but have had limited success. I found some functions online that convert julian date to regular date, but those don't seem to help me with my specific requirement.

I started off with a format of Julian Date/Time in cell A3, (example: 735188.468347616). I changed the format of cell A3 and ended up with "11/15/3912 11:14:25 AM". I am almost there, But I want the new format to read "11/15/2012 11:14:25 AM" instead of "11/15/3912 11:14:25 AM". In other words, it changed the format from Julian to regular on everything except for the year. I want the year to read "2012".

Thanks in advance for any tips...

Thanks but I already tried that one. Using the second formula on that page yields "9/8/1974"

Hi -

I just pasted your numeric value into one of my spreadsheets and put in the following custom date/time format and it works.

[$-409]m/d/yy h:mm:ss AM/PM;@

If that doesn't work, perhaps there is something odd about your spreadsheet. Please post it and I can take a stab at it.

Thanks. I ended up getting it. What I did was split the column that read "11/15/3912 11:14:25 AM" into two columns, one for date and one for time. I used =MOD(C3,1) for the time column and =INT(C3) for the date column. The date column still read "11/15/3912", so i just manually corrected it and filled down so the entire column read 11/15/2012, and a few 11/16/2012's at the bottom. I then merged the corrected date column with the time column using the formula =INT(E3)+MOD(H3,1). I guess this way wouldn't work well if you had lots of different dates since you'd have to manually correct them.

loginjmor-I couldn't get yours to work but maybe I am missing something. for example, I don't see where you plug in the numeric value into that formula. Anyway here is my spreadsheet, hope the attachment worked.