Date calculation error

[bdsii]

I am uploading a sample file (hopefully) that contains the problem code.

I have created a spreadsheet and supporting code to allow me to type a begin date and an end date and the macro will calculate the number of work hours between the two dates based on "calendar" tab in the spreadsheet.

I have a piece of code that checks to make sure the end date is after the start date for a sanity check for the user.

For some reason, I believe it could be a formatting issue, there are times when I get the error notice that "the End Date must be after the Start Date" incorrectly. Both dates were declared as Strings .


I get that error when I used the following data:
Start = 2/4/2010 and End = 12/31/2010
but not when I enter 1/4/2010 and 12/31/2010

I also get the error when Start = 9/1/2009 and End is 10/1/2009

The code to perform this check is:


If enddate < begindate Then
Sheets("Calculations").Select
MsgBox " End Date should be BEFORE Start Date !"
Exit Sub
End If



I used code to format the dates so that if you enter 9/1/2009 or 09/01/2009 it would convert to 9/1/2009 and work with the searches. That snippet of code is:


enddate = Range("D5").Value
enddate = Format(enddate, "Short Date")




I hope to maintain the code I have in the spreadsheet, I just need advice on the problem and how to correct it inside the code I have.

help...anyone ? :dunno

thanks!

[mbarron]

I haven't delved into the code. I'll take a look at it in a bit.
You can use the following to calculate the hours without using a Macro:
=SUMPRODUCT((Calendar!B2:B612>=Calculations!B5)*(Calendar!B2:B612<=D5)*Calendar!C2:C612) You can also use Validation to insure that the start and end dates are in the correct order.

[mbarron]

Since the macro is finding the beginning date and end date correctly and reporting their respective rows, you can have the macro compare those values instead of the date.

Change the
If enddate < begindate Then
to
If EndRow < StartRow Then

[Bob Phillips]

The startpage macro is not in the workbook

[bdsii]

I forgot the startpage macro module when I created the sample sheet....it only opened the worksheet I wanted opened.

mbarron - I will give your solution a try:


If EndRow < StartRow Then

but it is driving me nuts trying to figure out why it is acting that way. Even if it was being converted to a number, they would not be causing this error, would they ?

[mbarron]

I should have realized this earlier. It is happening because you are converting your actual dates to string representations. Any date between February (2) and September (9) will evaluate as after January(1), October(10), November(11), and December(12) since 2 through 9 are greater than 1 "alphabetically".

Change your begindate and enddate to Date types and get rid or the the two Format(???,"Short Date")s as they are not needed. you may also want to change the warning from:
MsgBox " End Date should be BEFORE Start Date !" to
MsgBox " End Date should be AFTER Start Date !"

[bdsii]

thanks mbarron, I thought that may be the problem but I also thought I would have a problem if I removed the formatting line if the user typed in the date in a different format. Since I made the changes, it doesn't seem to acting up now. Both your solutions worked but I went with the one to change the String to Date .

Thanks !