This post will guide you how to convert Julian date to a calendar date in Excel. How do I convert a given Julian Date to a calendar date with a formula or VBA Code in Excel.
1. What is Julian Date?
Julian day number is the integer assigned to a whole solar day in the Julian day count starting from noon Universal time, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar), a date at which three multi-year cycles started (which are: Indiction, Solar, and Lunar cycles) and which preceded any dates in recorded history.
2. Convert Julian Date to Calendar date Using Formula
If you want to convert Julian date to a calendar date for your date values, and you can use a formula based on the IF function, the LEFT function and the MOD function. Like this:
=("1/1/"&(IF(LEFT(B2,2)*1<20,2000,1900)+LEFT(B2,2)))+MOD(B2,1000)-1Type this formula into cell c2, and press Enter key on your keyboard to apply this formula.
Then go to HOME tab, select Date as Number Format in the Number group.
Keep to select the Cell C2, and drag the AutoFill Handle down to other cells to apply this formula to convert the Julian date format into Calendar date.
3. Convert Julian Date to Calendar Date with VBA Code
You can also use a User Defined function with VBA Code to convert Julian date to a Calendar date in Excel. just do the following steps:
Step1: open the VBA editor in Excel by pressing Alt + F11
Step2: Insert a new module and paste the below code into the module.
Function JulianToCalendar_ExcelHow(julianDate As Double) As Date
Dim year As Integer
year = IIf(Left(julianDate, 2) < 20, 2000, 1900) + Left(julianDate, 2)
JulianToCalendar_ExcelHow = DateSerial(year, 1, 1) + (julianDate Mod 1000) - 1
End Function
Step3: you can use the formula in a blank cell to convert it to a calendar date.
=JulianToCalendar_ExcelHow(B2)
This function takes a Julian date as input (in the format of a double) and returns a calendar date (in the format of a Date object).
4. Convert calendar date to Julian date using Formula
To convert a calendar date to a Julian date in Excel, you can use the following formula:
=TEXT(C2,"yy")&TEXT((C2-DATEVALUE("1/1/"&TEXT(C2,"yy"))+1),"000")where C2 is the cell containing the calendar date you want to convert.
Let’s See How this Formula Works:
- The TEXT function is used to convert the year portion of the date to a two-digit number in YY format. The formula TEXT(C2,”yy”) extracts the last two digits of the year from the date in cell C2.
- The DATEVALUE function is used to convert a text string in the format “1/1/YY” to a date value. The formula DATEVALUE(“1/1/”&TEXT(C2,”yy”)) returns the date value for January 1 of the same year as the date in cell C2.
- Subtracting the January 1 date value from the date value in cell C2 gives the number of days between January 1 and the date in cell C2.
- Adding 1 to this number gives the day of the year for the date in cell C2.
- The TEXT function is used again to format the day of the year as a three-digit number with leading zeros. The formula TEXT((C2-DATEVALUE(“1/1/”&TEXT(C2,”yy”))+1),”000″) returns the day of the year in DDD format.
5. Convert calendar date to Julian date with VBA Code
You can create a User Defined function with VBA Code to convert a calendar date to a Julian date in Excel, just do the following steps:
Step1: open the VBA editor in Excel by pressing Alt + F11
Step2: Insert a new module and paste the below code into the module.
Function ConvertToJulianDate_ExcelHow(myDate As Date) As String
Dim myYear As String
Dim julianDay As String
myYear = Format(myDate, "yy")
julianDay = Format(myDate - DateSerial(year(myDate), 1, 1) + 1, "000")
ConvertToJulianDate_ExcelHow = myYear & julianDay
End Function
Step3: You can then call the function from a worksheet cell or from another VBA macro.
For example, to convert the date in cell C2 to a Julian date in cell E2, you could use the formula in cell E2.
=ConvertToJulianDate_ExcelHow(C2)
The function calculates the Julian date by extracting the year from the calendar date using the Format function, and then subtracting the date of January 1 of that year from the calendar date, adding 1 to get the number of days since January 1, and then formatting the result as a 3-digit number using the Format function.
6. Video: Convert between Julian date and Calendar Date in Excel
In this video, you will learn how to convert between Julian dates and calendar dates in Excel using both formulas and VBA code.
7. Related Functions
- Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])…. - Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)…. - Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
