How to convert between Julian date and calendar date in Excel?
Generally, Julian date is a date format which includes 5 digit numbers, first two indicate the year, and last three indicate the day of the year. For example, 14001 indicates 1/1/2014 in calendar. Now this tutorial is talking about the methods on conversion between Julian date and calendar date in Excel.
Convert Julian date to calendar date
Convert calendar date to Julian date
Convert Julian date to calendar date
To convert Julian date to calendar date, you just need a formula in Excel.
1. In a blank cell, type this formula =DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+LEFT(A1,2),1,RIGHT(A1,3)) into it, press Enter key, if you need, you can drag the fill handle to a range with this formula. See screenshot:
Tip: A1 indicates the Julian date you need to convert to calendar date.
2. Then right click at these highlighted cells, and select Format Cells from context menu, and in the popped out Format Cells dialog, under Number tab, click Date in the Category list, then select the date type you need in the right section.
3. Click OK, then you can see all Julian dates have been converted to calendar dates.
Convert calendar date to Julian date
If you want to convert calendar date to Julian date, you can use below formulas.
In a blank cell, type this formula =TEXT(A1,"yy")&TEXT((A1-DATEVALUE("1/1/"&TEXT(A1,"yy"))+1),"000") and press Enter key, if you need you can apply this formula to a range by dragging the auto fill handle.
Tip: A1 is the calendar date you want to convert to Julian date.
Quickly convert nonstandard date to standard date formattiing(mm/dd/yyyy)
|
| In some times, you may received a workhseets with multiple nonstandard dates, and to convert all of them to the standard date formatting as mm/dd/yyyy maybe troublesome for you. Here Kutools for Excel's Conver to Date can quickly convert these nonstandard dates to the standard date formatting with one click. Click for free full featured trial in 30 days! |
 |
| Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. |
Relative Articles:
- Convert between date and unix timestamp in Excel
- Convert time string to time or date and time in Excel
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
