JDE Julian Date Converter Function for Excel

After I created “JDE Julian Date Converter Online“, I was asked for a function which could be directly used in excel as JDEtoSimple(A1) where A1 is the cell containing the JDE Julian Date value. So today I present you two functions, to convert from JDE Julian Date to Gregorian Date (DD/MM/YYYY) format and vice versa.

I have kept the names of the functions as short as possible for quick usage. But before you can use these functions in excel, you need to define them in excel. Please understand that there will be quite a few steps for you to follow to integrate these changes in excel. But once done with the initial setup then you can always use these functions in your Excel.

Go through the following steps to integrate these functions in your Excel.

1. Open a Excel Worksheet.
2. Press Alt+F11 to open Visual Basic Editor.
3. Press Ctrl+G to open Immediate Window.
4. Type ? application.StartupPath and Press Enter :This will provide you with the path to “XLStart” Folder. We need this to save the add-in file we are going to create. By saving the add-in file in this folder, the functions we will be creating can be used in all excel sheets and not just the one we define the functions in.
5. Now, click on “Insert” > “Module”
6. Paste the following code in the code window

   Function J2S(juliandate)
   'Author : Nimish Prabhu
   'Website : http://www.nimishprabhu.com
   Dim t As Date
   t = DateSerial(Year("01/01/" & Application.Text(1900 + Int(juliandate / 1000), 0)), 1, 1) + juliandate Mod 1000 - 1
   J2S = Format(t, "dd/MM/yyyy")
   End Function
   
   Function S2J(mydate)
   'Author : Nimish Prabhu
   'Website : http://www.nimishprabhu.com
   S2J = (Year(mydate) - 2000 + 100) * 1000 + mydate - DateSerial(Year(mydate), 1, 1) + 1
   End Function
   
   

   Note : If you want the output as MM/DD/YYYY instead of DD/MM/YYYY, replace the following line :
   

   J2S = Format(t, "dd/MM/yyyy")

   with

   J2S = Format(t, "MM/dd/yyyy")

   Note that the capital MM are intentional, do not replace them with mm, else you will get minutes in your output instead of month.

   

7. Now Save the workbook as “Add-in” (File > Save Workbook)
   Please do not forget to save the workbook in XLStart directory. Else the code won’t work in other workbooks.

   

8. That’s it. Now use the formula in any workbook
   =J2S(A1) to convert from JDE Julian Date to Simple Date (DD/MM/YYYY)
   =S2J(A1) to convert from MM/DD/YYYY to JDE Julian Date.

Hope this tutorial was helpful. Comment below in case of queries.