» 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:
- Definitive Guide to Excel VBA
- Financial Statement Analysis with S&P insert card
- The Analysis and Use of Financial Statements
- How to Use Financial Statements: A Guide to Understanding the Numbers
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Special Edition Using Microsoft Access 2002
No comments have been submitted.
