Excel VBA Date: Have Fun with Your Date!

Just to make sure you didn’t come here looking for tips to excel on the dating scene, let’s just get this straight: in this article we’re going to be doing a deep dive into the VBA programming for date in Excel! 🙂

VBA stands for Visual Basic for Applications. It’s basically Microsoft’s at integrating the event driven programing language Visual Basic, with their Microsoft Office application suite. VBA is most commonly used with MS Excel, as you can learn in this course. The most popular part of VBA is its Macros, but we’re going to take a side track and explore another important aspect – which is the date handling. (if you’re interested in learning more about Excel VBA Macros, do check out this course). We assume a basic level of familiarity with both Excel and Visual basic. If they’re new to you, please first take this basic VBA for Excel course. Alternatively, if you just need to refresh your concepts, you can read through this tutorial.

Let’s get started!

How Excel Internally Handles Dates

You may enter a date in any of the human readable formats, for example the American style (Month-Day-Year) or the European format (Day-Month-Year). Excel always stores these internally as serial values. Times are stored in decimal format. This works really well for those who use Excel extensively – since it makes it extremely easy to add dates or times, and even to subtract them. It takes away the tedium of having to mentally add up dates, and keep track of which month has 30 days versus 31 days, or remember that an hour is 60 minutes and time doesn’t follow the decimal system.

When to Use VBA

At times you may have to do a repetitive task, or want to automate it – that’s the most common case where VBA comes in handy. More advanced users would also use VBA to create their own algorithms to analyse their data and then use Excel’s graphs to show the data neatly (we do have the Ultimate VBA course that shows you just how to do this – check it out here!).

How to Set the Date with VBA

VBA has a simple macro to set the date. First you’ll have to declare a variable with the Dim statement. Then you can use the DateValue function, to assign a date to the variable. Let’s take a quick look at the code:

Dim Date1 As Date
Date1 = DateValue("Jan 19, 2014")
MsgBox Year(Date1)

Bonus: As you see in the example, you can use the MsgBox command at the end to display the “Year”, that’s 2014, in a pop-up message! How cool is that!

How to add 2 Dates in VBA

You can add two dates, or a number of days, or a number of months, or a quarter – basically any unit of date) using the very versatile DateAdd function. Let’s look at an example:

Dim Date1 As Date, Date2 As Date
Date1 = DateValue("Jan 19, 2014")
Date2 = DateAdd("d", 7, Date1)
MsgBox Date2

Pay attention to the syntax of the DateAdd function.

• The ‘d’ indicates we want to add a number of days.
• The ‘7’ indicates we want to add 7 days.
• The ‘Date1’ indicates that we want to add 7 days to Date1.

Thus we’ve created a new date, Date2 by adding 7 days to Date1, with the DateAdd function.

Like we said earlier, instead of days, you can also add months, quarters or just about anything else. These are the options the DateAdd function can take

• yyyy: Year
• q: Quarter
• m: Month
• y: Day of year
• d: Day
• w: Weekday
• ww: Week
• h: Hour
• n: Minute
• s: Second

Take a break from this tutorial and go try some of them out for yourself!

How to Subtract a Date in VBA

Make a wild guess. How do you think you can subtract dates in VBA? What would this function do?

Dim Date1 As Date, Date2 As Date
Date1 = DateValue("Jan 19, 2014")
Date2 = DateAdd("d", -7, Date1)
MsgBox Date2

Getting the Date from a String

Sometimes you may have the date saved as a string in your Excel spreadsheet – for example “May/12/2014”. For you to be able to order it, sort it, or add or subtract it, you’ll need to convert into an actual date format. Here’s how you can do that.

Dim Date1 As Date
Date1 = DateValue("19/Jan/2014")
Range("A1").Value = Date1

This will take the string “19/Jan/2014” and internally convert it to the date “19/1/2014” (or “1/19/2014” depending on whether your default is set to American or European format).

Bonus: You can call “Range” to save the date, Date1, to cell A1.

How to Avoid Date Format Issues

Depending on which part of the globe you’re at, you use either the American style (Month-Day-Year) or the European format (Day-Month-Year). Ever faced a situation where the two got mixed up – when you wrote 10/12/2013 meaning 12th October 2013 and a vendor or supplier on the other side of the globe thought you want the delivery on the 10th of December 2013?  VBA has a way to make sure you avoid such goof ups – if you use it right. There’s a DateSerial function, which forces you to give the date in a specific, neutral format, thereby avoiding all ambiguity. The syntax is

DateSerial (year, month, day)

Here’s how you can use it:

Dim Date1 As Date
Date1 = DateSerial(2014, 1, 19)
Range("A1").Value = Date

There’s a lot more that can be done with dates in VBA. This is just the tip of the iceberg. We strongly recommend you experiment with VBA for yourself.  You could always flex some muscle with MrExcel in this VBA course, or take a leap ahead with this Ultimate VBA course. Have fun, and let us know how it goes!