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.

Table of Contents

- 1. What is Julian Date?
- 2. Convert Julian Date to Calendar date Using Formula
- 3. Convert Julian Date to Calendar Date with VBA Code
- 4. Convert calendar date to Julian date using Formula
- 5. Convert calendar date to Julian date with VBA Code
- 6. Video: Convert between Julian date and Calendar Date in Excel
- 7. Related Functions

## 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)-1`

Type 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)…

## Leave a Reply

You must be logged in to post a comment.