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 400772. 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 10I 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

exactlythree years orexactlyfive 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

Assume start is in A1, end is in B1. Conditional formatting (I did this on B1, but you can do it on any cell) =NOT(OR(DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))=B1,DATE(YEAR(A1)+5,MONTH(A1),DAY(A1))=B1,DATE(YEAR(A1)+10,MONTH(A1),DAY(A1))=B1))

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.

oh depending on how your query works you might need to put a -1 after every occurence of DAY(A1) =NOT(OR(DATE(YEAR(A1)+3,MONTH(A1),DAY(A1)-1)=B1,DATE(YEAR(A1)+5,MONTH(A1),DAY(A1)-1)=B1,DATE(YEAR(A1)+10,MONTH(A1),DAY(A1)-1)=B1))

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

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History