Microsoft Excel 2007 is a powerful spreadsheet program that can sort and classify huge amounts of data. While Microsoft Excel 2007 comes with many built in functions, it doesn't have a built in function to convert Julian dates. Julian dates are often used in computer software, and are a combination of a two-digit year and three-digit number representing the day of the year. For example, 99001 is January 1, 1999. While Julian dates are easy for a computer to read, they aren't as easy for you to decipher. Excel allows you to convert between Julian and regular dates with the addition of a formula into your spreadsheet.
- Skill level:
Other People Are Reading
Click on the cell were you would like your output data to start. For example, if you have a list of Julian dates in column A, you might want your regular dates to start on column B, so click on cell B1.
Enter the following formula into the cell. =("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1. The formula returns regular dates for the A column: tailor the formula to your cell range. For example, if you data is in column B, change the "A"s in the formula to "B"s.
Copy the formula into as many cells as you want dates outputted. For example, if you have Julian dates in cells A1 through A50, copy the formula 50 times from cells B1 to B50.
Highlight the cells you created in Step 3.
Right-click on the highlighted cells and choose "Format Cells."
Click on "Date" from the "Number" tab. This tells Excel to convert the formula's output into regular calendar dates. Choose the date type (for example, 14 December, 2009 or 14 Dec) and click "OK."
Tips and warnings
- A fast way to copy cells is to drag the fill tool (the little black square in the bottom right corner of the cell) across the cells you want the formula copied to.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for