How to Convert Julian Date to a Calendar date in Excel

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

Type this formula into cell c2, and press Enter key on your keyboard to apply this formula.

convert julian date to calendar date1

Then go to HOME tab, select Date as Number Format in the Number group.

convert julian date to calendar date2

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.

convert julian date to calendar date3

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.

How to Convert Julian Date to a Calendar date vba 3.png
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)
How to Convert Julian Date to a Calendar date vba 4.png

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.

How to Convert Julian Date to a Calendar date 10.png

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.

How to Convert Julian Date to a Calendar date vba 1.png
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)
How to Convert Julian Date to a Calendar date vba 2.png

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

Compare Dates

This post will guide you how to compare dates if it is greater than a given date in excel. How do I compare a list of dates with a specified date using a formula in Excel.

Compare Dates


Assuming that you have a list of data that contain date values in Excel, you can use the IF function to create a formula to achieve it. If the date is greater that the given date value, then return True. Otherwise, it returns False.

The formula is like as below:

=IF(B1>DATEVALUE(“4/20/2018″),”True”,”False”)

Type this formula into a blank cell and then press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula to compare dates.

compare date1

The DATEVALUE function will convert the date to a serial number. So that if can do the comparing operation. And the IF function will compare the date value in the cell B1 with the serial number returned by the DATEVALUE function.

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 DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

 

Convert Date to Text

This post will guide you how to convert dates stored as text format in Excel. How do I convert the date as date format to text format in excel. You can use a variety of formulas to convert the dates in your worksheet to text format.

You can use the TEXT function to create a formula to convert the date as date format to text format.

Assuming that you have a list of data that contain date values and you want to convert its format as text format. Just do the following steps:

#1 type the following formula into a blank cell.

=TEXT(B1,”yyyy-mm-dd”), press Enter key in your keyboard

convert date to text1

#2 drag the AutoFill Handle over other cells to apply this formula.

convert date to text2

If you want to convert a date as Text format to number, you can use the DATEVALUE function or just use the Format Cell function to achieve it. Such as: =DATEVALUE(“2018/23/08”)

convert date to text3

Related Functions


  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

Convert Month Name to Number

This post will guide you how to convert month name to number in excel. How do I convert month name to number with an excel formula. How to convert month name to number with an Excel VBA macro. How to convert 3 letter month abbreviation to number in excel. How to convert 3 character month to number in excel.

Assuming that you have a list of data B1:B12 contain month names and you want to convert all month names to numbers, how to achieve it.

Convert Month Name to Number with Excel Formula


If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function. Like this:

=MONTH(DATEVALUE(B1&"1"))

Type this formula into the formula box of a blank cell C1, then drag the AutoFill handler over other cells to apply this formula.

convert month name to number1

You will see that all month names have been converted to numbers in range of cells C1:C12.

Convert Month Name to Number with VBA code


You can also use an excel VBA Macro to convert month name to number in excel. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

convert column number to letter3

#4 paste the below VBA code into the code window. Then clicking “Save” button.

convert month name to number2

Sub convertMonthNameToNumber()
    Dim myRange As Range
    Dim selectedRange As Range
    windowTitle = "convert month name to numbers"
    Set selectedRange = Application.Selection
    Set selectedRange = Application.InputBox("Select one Range that contain month names", windowTitle, selectedRange.Address, Type:=8)
    For Each myRange In selectedRange
        If myRange.Value <> "" Then
            myRange.Value = Month(DateValue("03/" & myRange.Value & "/2018"))
        End If
    Next
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

convert month name to number3

#6 select cells that you want to convert, such as: B1:B12. Click Ok button.

convert month name to number4

#7 Let’s see the result.

convert month name to number5

Convert Number to Month Name with Excel Formula


If you want to conert number to month name, you can use an formula based on the TEXT function, and the DATE function to achieve it. Like this:

=TEXT(DATE(2018,B1,1),"mmmm")

Type this formula into a blank cell, and then press enter key, then drag the AutoFill Handler over other cells.

Let’s see the result.

convert month name to number6

Related Functions


  • Excel MONTH Function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…
  • Excel DATE function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
  • Excel Text function
    The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…

 

 

Excel DATEVALUE Function

This post will guide you how to use Excel DATEVALUE function with syntax and examples in Microsoft excel.

Description

The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.

For example, you can convert a date “2018/03/04” that is text format to a serial number, you can use the formula: =DATEVALUE (“2018/03/04”). Then it returns a serial number “43163” you can use the Excel dates to manipulate with formulas or pivot table to filter, sort. So it is very useful than the text dates.

The DATEVALUE function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The DATEVALUE function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the DATEVALUE function is as below:

=DATEVALUE(date_text)

Where the EDATE function arguments is:

  • Date_text -This is a required argument. A valid date that the format is text. Or the text that represents a date in an excel date format.

Note:

  • The date_text argument must represent a date between January 1, 1900 and December 31, 9999. The DATEVALUE function returns the #VALUE! Error value if the value of the date_text argument falls outside of this range.
  • If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer’s built-in clock. Time information in the date_text argument is ignored.
  • January 1 1900 is serial number 1 by default.

Excel DATEVALUE Function Examples

The below examples will show you how to use Excel DATEVALUE Function to return a serial number of a given date.

1# Get a serial number of a date (03/14/2018) the format as text, enter the following formula in Cell B1.

=DATEVALUE("03/14/2018")

excel datevalue examples1

2# Get a serial number of a date (14-May-2018) the format as text, enter the following formula in Cell B2.

=DATEVALUE("14-May-2018")

excel datevalue examples2

3# Get a serial number of a date (2018/03/14) the format as text, enter the following formula in Cell B3.

=DATEVALUE("2018/03/14")

excel datevalue examples3

4# get a serial number from a date that the year portion is omitted (03/04) assuming the current year is 2018, using the following formula:

=DATEVALUE("03/14")

excel datevalue examples4

5# get a serial number from a date that created by combining the values in Cells A1, A2, and A3

=DATEVALUE(A1&"/"&A2&"/"&A3)

excel datevalue examples5

Related Functions


  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…

More Excel Datevalue Function Examples


  • Convert Month Name to Number
    If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function…
  • Compare Dates
    Assuming that you have a list of data that contain date values in Excel, you can use the IF function to create a formula to achieve it. If the date is greater that the given date value, then return True. Otherwise, it returns False….

Excel MONTH Function

This post will guide you how to use Excel MONTH function with syntax and examples in Microsoft excel.

Description

The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. And you can use the MONTH function to get a month component from a given date value. And you can feed the month number to another formula.

The MONTH function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The MONTH function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MONTH function is as below:

=MONTH (serial_number)

Where the MONTH function arguments is:

  • Serial_number -This is a required argument. A valid date value that contains the month component you want to extract. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE (2019,3,15) for the 15th day of March, 2019.

Note:

  • A serial date is how the Microsoft Excel stores dates and it represents the number of days since 1900-01-01, so the January 1, 1900 date is serial number 1 by default.
  • If the serial_number is not a valid Excel date, it will return #VALUE! Error.

Excel MONTH Function Examples

The below examples will show you how to use Excel MONTH Function to return an integer number that representing the month component of a given Excel date

1# get month of the date in Cell A1, enter the following formula in Cell B1.

=MONTH(A1)

excel month examples1

2# convert Month name to number, type the following formula in Cell B1.

You should use the DATEVALUE function to convert a date stored as text to a serial number. Then feed the result into the MONTH function to extract a month number from date.

=MONTH(DATEVALUE(A1 & "1"))

excel month examples2

Related Functions


  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

More Excel Month Function Examples


  • Convert Month Name to Number
    If you want to convert month name to number with an excel formula, or you want to convert 3 letter month name to numbers, you can create a formual based on the MONTH function and the DATEVALUE function…
  • Count Dates in Given Year/Month/Day in Excel
    You can create a formula based on the SUMPRODUCT function and the YEAR function to count dates by a give year….

Excel TODAY Function

This post will guide you how to use Excel TODAY function with syntax and examples in Microsoft excel.

Description

The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The serial number is the date-time code used by Excel for date and time calculations.

You can use the TODAY function to get the current date displayed on your worksheet, and regardless of when you open the workbook. And it also can be used for calculating intervals. For example, if you want to know that person’s age (he was born in 1984), you can use the following formula:

=YEAR(TODAY())-1984

This formula will use the TODAY function as an argument for the YEAR function to get the current year, and then subtracts 1984, getting his age.

The TODAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.

The TODAY function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the TODAY function is as below:

=TODAY()

There are no any arguments for the TODAY function, but you still need to use empty parentheses ().

Excel TODAY Function Examples

The below examples will show you how to use Excel TODAY Function to return the current system date.

1# using the TODAY function to get the current date, enter the following formula in Cell B1.

=TODAY()

excel TODAY examples1

2# Get the current date and then subtracts 10 days, using the following formula:

=TODAY() -10

excel TODAY examples2

3# Get the number of days between the current date and 03/14/2019, type the following formula:

=DATEVALUE("03/14/2019")-TODAY()

excel TODAY examples3

4# Get the current day of the month, using the following formula:

=DAY(TODAY())

excel TODAY examples4

5# Get the current month of the year, type the following formula in Cell B1:

=MONTH(TODAY())

excel TODAY examples5

You can press F9 key to force the worksheet to recalculate each time and then update the value. If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the workbook or worksheet recalculates. On the File tab, click Options, and then in the Formulas category under Calculation options, make sure that Automatic is selected.

excel TODAY examples6

Related Functions


  • Excel DAY function
    The Excel DAY function returns a day of a date (from 1 to 31). The DAY function is a build-in function in Microsoft Excel and it is categorized as a DATE and TIME Function.The syntax of the DAY function is as below:= DAY (date_value)
  • Excel YEAR function
    The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…
  • Excel MONTH function
    The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12. The syntax of the MONTH function is as below:=MONTH (serial_number)…
  • Excel DATEVALUE Function
    The Excel DATEVALUE function returns the serial number of date. And it can be used to convert a date represented as text format into a serial number that recognizes as a date format.The syntax of the DATEVALUE function is as below:=DATEVALUE(date_text)…

More Excel TODAY Function Examples


  • Conditional Formatting date with red Amber or Green
    How to highlight the date with red if the cell dates is past now(). How to highlight the date with amber if the cell date is past now but within the next 6 months from now(). How to highlight the date with green if the cell date is more than 6 months from now…
  • Highlight Rows If Dates Have Passed
    Assuming that you have a worksheet that have a bunch of columns and each row has a date column. If the date in the date column goes past from now on and you want to highlight the entire row with red color….
  • Count Dates in Given Year/Month/Day in Excel
    You can create a formula based on the SUMPRODUCT function and the YEAR function to count dates by a give year….

Excel IF formula with “Equal to” logical operators

The “Equal to” logical operator can be used to compare the below data types, such as: text string, numbers, dates, Booleans.  This section will guide you how to use “equal to” logical operator in excel IF formula with text string value and dates value.

Formula example Description
=IF(A1=B1,“TRUE”,”FALSE”) Returns TRUE if a value in cell A1 is equal to the value in cell B1, FALSE if not.
=IF(A1=”excehow”,”TRUE”,”FALSE”) Returns TRUE if a value in cell A1 is equal to “excelhow”, FALSE if not.
=IF(A1=TRUE,”TRUE”,”FALSE”) Returns TRUE if a value in cell A1 is equal to TRUE, FALSE if not.
=IF(A1=(B1/5),”TRUE”,”FALSE”) Returns TRUE if a value in cell A1 is equal to the quotient of the division of B1 by 5, FALSE if not.

 

The Generic IF formula using “equal to” logical operators with text values

Let’ see the below formula:

=IF(B1=”excelhow”,True_result, False_result)

Excel IF formula with Equal to logical operators2

If you want to do some special action when a value in a cell equal to a certain value, then you can use IF function with logical operator to constructing a condition statement. If the condition statement is true, then do some action.

The logical test is B1=”excelhow”in the above formula, and if cell B1 is equal to the string “excelhow”, then returns “True_results“; If FALSE, return “False_result”.

We want to set the value in Cell D1, we only need to take action when the condition test is TRUE. Here, we will add“True_result” string to Cell D1 when the value in cell B1 is “excelhow”. If the value in cell B1 is not “excelhow”, then we will add “False_result” string into Cell D1.

The Generic IF formula using “equal to “   logical operators with dates value

If the cells B1 and B2 contain the date value “10/29/2018”, if we use logic test B1=”10/29/2018” in the formula =B1=”10/29/2018”, you will get the FALSE result. Or if you use logic test B1=B2 in formula =B1=B2, then it will return TRUE.

So if you want to check one value in cells, you must use DATEVALUE function to warp a date to compare with a specific date value. For example: =B1=DATEVALUE(“10/29/2018”), at this time, it will return TRUE.

Now we can write a Generic IF formula using “equal to” with date value as follows:

=IF(B1= DATEVALUE(“10/29/2018”),”excelhow”,”google”)

Excel IF formula with Equal to logical operators2

The Generic IF formula using “equal to “   logical operators with Boolean values and numbers

The Boolean value of TRUE Values equates to 1 and The FALSE value is equal to 0.

When writing a logical test using “equal to” operator that compares a Boolean value and a number. Assuming that using the logical test “1=True”, you will get the False result. it’s not our expected result.  At this time, you can use the double minus sign in the front of a Boolean value in a logical test. For example:”1=–TRUE

=IF(A1=--B1,"excelhow.net","google.com")

Excel IF formula with Equal to logical operators2


Related Formulas

  • Excel IF formula with operator : greater than,less than
    how to use if function with “greater than”,  “greater than or equal to”, ”less than” and “less than or equal to” logical operators in excel.Let’s see the below generic if formula with greater than operator:=IF(A1>10,”excelhow.net”,”google”) …

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….
  • Excel nested if function
    The nested IF function is formed by multiple if statements within one Excel if function. This excel nested if statement makes it possible for a single formula to take multiple actions…