Hello, and welcome to another blog post in my #MacroMonday series. Are you looking for a Julian date converter? Do you need to know how to write a macro that converts dates? In this blog post, we will show you how to do both. We will also provide a function that you can download and use. Let’s get started!
So, creating your own Excel VBA function can be very useful. Excel has a LOT of formulas and functions. But, sometimes, writing your own can save even more time and effort. You need a UDF. (User Defined Function). We can achieve this with some simple VBA code.
User Defined Function. Rewind.
Before we go any further in creating our own Excel function, let’s take a step back. When we talk about VBA Macros in Excel, we usually, or at least most of the time are talking about Sub procedures. Most of the macros we record or write in VBA are Sub procedures. Sub is a command. So, executing a Sub procedure and what happens depends on the code you write as part of that Sub procedure.
But, we also have a Function which is also a procedure, but quite different from the Sub. Take the Excel VLOOKUP, SUM, INDEX, MIN, MAX. These functions usually take at least one argument and do some calculations in the background to return a single value. That is a function
So, just to clarify the difference between a Sub Procedure and a Function.
Sub vs Functions.
⇒A Sub procedure is a group of VBA statements that performs a single or group action in Excel.
⇒A Function procedure is a group of VBA statements that perform a calculation and, like the definition of an Excel Function, will return a single valuee.
So, let’s write some code to solve our problem of converting Julian dates.
Step 1. Open Visual Basic | Insert New Module.
The first step is to open the Visual Basic Editor. You can do this either by hitting ALT+F11 or hit the Developer Tab | Code Group | Visual Basic. Next, insert a new module to store your VBA function code. This is easy. Select the Insert menu then insert a new Module.
Step 2. Name the Function.
The second step is to name your function. Type Function then the name of the UDF. I have chosen to name the function JulianConvert. Makes sense right?. Notice that Excel automatically inserts the End Function code as a second line of code. The result of this function is a string. Other lines of VBA sit between these first and last code lines.
Function JulianCon(JulianAs String) As Long
End Function
Step 3. Declare Variables.
The third step is to declare any variables. Declaring variables will ensure that Excel allocates a section of memory to each of these values. In this function there are three variables.
Dim Year As Integer, Day as Integer, CD as Long
Step 4. Converting The Julian Date.
This next step is the calculation or conversion of the julian date. We use the CInt Function. This function will convert a value to an integer. For the year the left function takes the first four characters form the start of the string. To calculate the day the same clnt and right function takes the last three characters from the end of the string.
Year = CInt(Left(Julian, 4))
Day = CInt(Right(Julian, 3))
CD = DateSerial(Year, 1, Day)
Step 5. Use DATESERIAL To Return The Date.
In this next step the DATESERIAL function is a built-in function in Excel. DATESERIAL function returns a date given a year, month, and day value.
CD = DateSerial(Year, 1, Day)
JulianCon = CD
Step 6. Ending The Function. Julian Date Converter.
The last line of the code is the End Function once Excel has returned the result of the function. This line of code already is in place as a result of the first line of code written in Step 2. Now it is time to test the function.
Exit out of the visual basic editor. You can now text the function. The function is now available as i every other Excel function. Begin by typing the equals sign the the name of the function.
The next step in using the function is to simply select the cell that contains the Julain date to convert. Then, close the brackets and hit enter. The date is now converted. How awesome is that?. Our very own Excel function to re use anytime.
