Chad's Macro Mania
~~Chad K. Welch

Thanks Don!

Chad,

I do a little bit of VBA programming and have learned quite a bit from your column.  I have a suggestion for you to cover… Dates.  I can’t seem to understand how they work.  Oh, and calculations such as time between… forget it! 

Thanks,
Julie

Thank you, Julie, for a good suggestion.  Dates have been a stumbling block for a lot of people.  However, I’m sure that once you understand how they work, you’ll agree they really aren’t that hard to use.

Dates in Excel

First of all, the basic unit of a date used in all Microsoft products is the day.  One day equals one unit. If we subtract one day from the previous day we will get one.  To show that, do the following in Excel:

You’ll notice that 1 is shown in cell A3.  In fact enter any two dates in A1 and A2.  A3 will display the days between the dates. 

How about times?  They can be calculated the same way.  What is the basic unit of time in Microsoft?  The day.  That means that each hour, minute and second can be represented as a fraction of a day.  One hour is 1/24 of a day.  One minute is 1/(24*60) or 1/1440 of a day.  Enter two times in cells A1 and A2 on the same worksheet used above.  For example enter 3:00 PM in A1 and 12:00 PM in A2.  Cell A1 will show 0.125 as the difference.  That, incidentally, is 3/24.

Unfortunately, it isn’t as convenient to display the months or years between dates.  After all, one day will always have 24 hours, but how many days are in a month? 28, 29, 30 or 31?  I’ll let you in on a little secret called the DATEDIF formula that will display the differences very easily.  It must be a secret since it has been available since version 95, but has never appeared in the function list in Excel.  The function is described very well in the help files included with version 2000, so I won’t take the time to describe it here. 

Formats

The biggest problem that people have understanding dates in Microsoft products is the way it chooses the cell formats.  Let’s look at the first example above again, but reverse the steps.  Open a new worksheet and do the following:

Cell A3 should display 1-Jan-00.  Excel tries to be smart.  If you are using dates in your function, it will display the result as a date as well.  Select the cell and choose Cells from the Format menu.  Then choose the general or a number format.  Click OK and cell A3 should show 1 as expected. 

Dates can be simple if you know the format that the result should be displayed in.  The second example above displayed 0.125.  That’s all fine and dandy unless you don’t want the result displayed as a fraction of a day.  Suppose we wanted it displayed as 3:00 meaning three hours zero minutes.  Choose Cells from the Format menu again.  In the list there is a Time category, however there isn’t a format that will suit our needs.  Instead select the Custom category.  In the type box type [h]:mm.  Click OK and cell A3 will display 3:00 as we would like.  As you might guess, in the custom formats, h is for hour, m for minutes, s for seconds, d for days, M for months and y for years.  So if you wanted a format to display Thursday, July 4, 2002 7:00 PM use the custom format dddd, MMMM d, yyyy h:mm AM/PM.  Look up “Number Format Codes” in the help files for more information. 

This month we haven’t done anything with VBA.  I decided to plow the way with dates in general.  Next month we’ll take a look at some of the quirks and nuances of using dates in VBA. 

Remember, the offer still stands, $5.00 to anyone who sends me a question or idea that I can use in this section. Just send it to chad@welchkins.com and put "Idea for ABC" in the subject line.

Chad K. Welch works as a technician/enabler in Utah.  He is available for consulting or application programming with Microsoft Office and VBA.  Do you have a question or tip you’d like to have Chad address in this column?  Send an email to linda@personal-computer-tutor.com or contact him directly at chad@welchkins.com for more information.