» Converting a Julian Date to a Calendar Date
CATEGORY - Excel Date & Time Formulas
VERSION - All Microsoft Excel Versions
Problem:Listed in column A are Julian dates in "YYJJJ" format.
"YY" represents a year between 1920 and 2020, and "JJJ" represents the serial number of the day within the year.
We want to convert each date in column A to its corresponding calendar date.
Solution:
Use the LEFT, IF, and MOD functions as shown in the following formula and format the results as dates:
=("1/1/"&(IF(LEFT(A2,2)*1<20,2000,1900)+LEFT(A2,2)))+MOD(A2,1000)-1
Book Store:
Recommended Books:
- Finance and Accounting for Nonfinancial Managers
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
- Guide to Financial Reporting and Analysis
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Infectious Greed: How Deceit and Risk Corrupted the Financial Markets
- Fish! A Remarkable Way to Boost Morale and Improve Results
No comments have been submitted.
