Did you know that dates stored in Excel and Microsoft Access are numeric values? Although they look like dates to us, they are stored as a serial number behind the scenes. This makes it quick and easy to use dates in calculations in your VBA code. Let’s take a look at some examples.
I’ll start with Excel. I’ve created a new worksheet, and applied a custom date format to cell A1. This format displays both the date with a four-digit year, and the time with AM or PM:
Now I'll use the "Immediate" window in VBA to populate values into that cell.
Note: If you're not familiar with the Immediate window, you may want to google it. It's a great tool for testing and debugging your code. You can access the window by choosing View --> Immediate Window on the VBA menu.
In the Immediate window, I'll set the value of cell A1 to 1. That's it - just the number 1. Any guesses on what we'll see in cell A1?
Interesting, right? It turns out that 1 is equivalent to January 1, 1900 at midnight.
What do you think will happen if I set the date to 2?
Yup. January 2, 1900 at midnight is #2.
Why?
This happens because by default, Excel uses something called the "1900 date system", whereby January 1, 1900 is considered day 1, and every subsequent date is relative to that. So, if you knew the number of days between January 1, 1900 and today, you could simply type in that number, and the cell would be set to today's date.
I don't happen to know that number off the top of my head, but let's try another number that'll help make this clear: 366:
Date 366 is December 31, 1900. Note that it's not 365, because 1900 was a leap year.
Lest you think this applies only to the year 1900, you can see here that when I add two more to that number, I end up on January 2, 1901:
And Vice Versa...
Of course, I can do the reverse as well. Once I set a date into a cell in Excel, I can use VBA to determine the number that equates to it, although I have to use one of the built-in functions to do so. To demonstrate, I'll set cell A1 to today's date, and then retrieve the value in the Immediate window, first without using a built-in function:
VBA is very user-friendly, so it wants to show you a date in a typical date format, as we saw above. It's very inclined to keep the actual number behind-the-scenes. But we can use the "clng" function to convince it to show us the actual number:
Now we can see the actual number that is associated with January 9, 2023. I can see that it's been 44,935 days since January 1, 1900.
So What?
I'll bet you're thinking: so what? Why do I care?
Because this features makes it possible to perform calculations on your dates. You may already be familiar with the DateAdd and DateDiff functions, which are useful. But this gives you a shortcut.
Let's say you need to calculate a due date that is 30 days from now. That looks like this:
What if you've got an Order Date, and you want to know how many days ago the order was placed?
Cool, right?
What about time?
Taking this a step further, let's consider the time. We know that "1" is January 1, 1900 at midnight. But what about 12:01 am?
That's right. The time is a fraction of that number. 1 minute is the same as about .001.
It's a little more complicated than that, though. You'd think that .002 is 12:02, and you'd be right. But is .003 12:03? Nope:
What's going on here? Fractions. As humans, we tend to think of time in terms of minutes and hours. But Excel thinks of time in terms of fractions of day. So, if a day is 1, then an hour is 1/24. The fractional representation of 1/24 is about .4166666667. Divide that by 60 to get minutes, and you get about .00069444.
So, if I want the value in cell A1 to be January 1, 1900 12:01 AM, I set it to 1.00069444:
Granted, this math isn't nearly as easy, but still, you can see how you could set up various calculations to arrive at a date or time, or to calculate the days, hours or minutes between two values.
Access Too?
I've used Excel for all of these examples, but the same is true in Microsoft Access - sort of. The one big difference is that in Microsoft Access, day #1 is December 31, 1899. This is due to a weird bug related to Leap Year in 1900, but I won't get into that here. The bottom line is that calculations on dates are equally viable in Access.
Wrap Up
I hope you can find some use for this interesting tidbit of information, and definitely shout out in the comments if you do.
In my next VBA blog, I'm planning to talk about type conversion. In this article, we saw the "clng" function, which converts a value to a Long data type. There are lots of twists and turns when it comes to data types in VBA, so next time, we'll be looking at that. To be notified when that post is live, subscribe using the form below.
As always, reach out to me if you'd like some help with your Excel or Access project. We can go over your project via Zoom or similar, and discuss your next steps.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #smallbusiness #excel #microsoftexcel #msexcel #vba #excelvba
© North Port Solutions, LLC, 2023. All rights reserved.
Comments