Dates in VBA

Microsoft Excel 2003 (full product)
September 23, 2009 at 13:34:58
Specs: Windows XP
I would like to add a section to a form where users can input a start date and an end date. A macro would calculate the difference between the start date and end date and would prompt the user for an explanation if it was an odd input (standard inputs are 3, 5 or 10).

I originally did this using conditional formatting of the cells, but my boss would like for me to create a single form where users could just input information.

Any help would be much appreciated.

See More: Dates in VBA

Report •

September 23, 2009 at 15:18:42

There are three main things to know about Dates and Times in Excel
1. Excel holds dates and times as a single decimal number, with the decimal part as the time from 0 to almost 1.
0.5 is 12 noon , i.e. half way through the day.
The integer or whole number part counts days since the start of 1900.
20 September 2009 is held as 40076 and 21 September 2009 will be 40077

2. What you see in a cell depends on how you format it. Formating such as "dd/mm/yyy hh:mm:ss AM/PM"
does not change the value stored.

3. When you enter data into a cell, Excel tests it for several things, including whether it looks like a date or time. If you enter 10:15 Excel will consider it a time and save it as 0.42708333 and format the cell as "h:mm". (Format may vary depending on default date and time settings - but you get the basic idea.

To get the number of days between dates just subtract one date from the other using only the integer part of the number.

I notice that you refer to conditional formatting again.

I think that you are trying to use conditional formatting for something it wasn't intended for.

Excel has a vast number of functions that can be used in cells and an equivalent number of commands in VBA. Conditional; formatting is really only to format cells, and not a way to perform calculations.

The date calculation above, using standard commands is simple.

If you want to highlight a certain value from the above calculation, such as an outlier result then a conditional format is useful.


Report •

September 23, 2009 at 18:44:27
Could you explain these 2 parts of your question?

1 - users can input a start date and an end date
2 - standard inputs are 3, 5 or 10

I don't see the connection between "3, 5 or 10" and "a start date and an end date"

Report •

September 24, 2009 at 05:54:07
Well 3 or 5 or 10 would be possible values for difference between the start date or end date. I know that the difference between two dates that are five years apart are 1825. So I would like Excel to highlight the cell a certain color whenever the cell is NOT 1,095 (3 yrs), ~1825 (5 yrs) or ~3652 (for 10 years).

thank you for your explanation though Humar

Report •

Related Solutions

September 24, 2009 at 06:19:14

How accurate do you years have to be?

Is three years 3*365
does it take account of leap years
or is it 3 years +/- an allowance; say 3 years +/- 30 days

The logic for this is needed !


Report •

September 24, 2009 at 06:57:27
I would like it to take account of leap years ( I didn't know that was possible...)

Report •

September 24, 2009 at 08:59:35

You still need to identify how accurate this is.

Is it exactly three years or exactly five years?

Leap years follow a defined formula, so they can be calculated if necessary, or you can lookup the day after each 28-Feb. e.g. =MONTH(C3+1) where C3 contains the date e.g. 28-feb-2004
If the year is a leap year the result is 2, else it returns 3 (result cell formatted as a number, not a date).


Report •

September 24, 2009 at 23:24:46
Assume start is in A1, end is in B1. Conditional formatting (I did this on B1, but you can do it on any cell)


Note the only reason this wouldn't work is if you put the 29th of Feb in as the start date, which I assume is unlikely enough not to worry about.

Report •

September 24, 2009 at 23:28:51
oh depending on how your query works you might need to put a -1 after every occurence of DAY(A1)


So if you put in 01/01/12 as the start date it would expect an end date of 31/12/2014, 31/12/2016 or 31/12/2021

Report •

Ask Question