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.

Hi, 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. e.g., =INT(B17)-INT(C17) 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. Regards

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"

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

Hi, How accurate do you years have to be? Is three years 3*365 or 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 ! Regards

I would like it to take account of leap years ( I didn't know that was possible...)

Hi, 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). Regards