occurance of time frame between two dates

[boomer1974]

Good day experts,

I have a simple sub here it adds to "y" a given amt for each time each weekday occurs between two dates. I now need to do this by the hour. Everytime the clock rolls from 5:00 PM to 6:00 PM I have to add to "y" I realize I can do this by formatting and looping through every hour, however this is very slow. Would anyone have any suggestions on a more efficient way ? As always, any help would be greately appreciated.

For x = Date1 To date2

If Weekday(x) = 1 Then y = y + 690
If Weekday(x) = 7 Then y = y + 1380
If Weekday(x) = 2 Then y = y + 180
If Weekday(x) = 3 Then y = y + 180
If Weekday(x) = 4 Then y = y + 180
If Weekday(x) = 5 Then y = y + 180
If Weekday(x) = 6 Then y = y + 180
Next x

 

[KenReay]

Hi

Maybe I am missing something here, but surely the number of occurences of 05-00pm is same as number of elapsed days so

Abs(DateDiff("d",Date1,date2))+1

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[boomer1974]

you are absolutely correct but date1 could be
10/13/04 9:00 AM and date2 could be 12:00 AM in which case 5:00 did not hit.

 

[PHV]

And what about this ?
Int(date2 - Date1) + (Format(date2, "hh") < "05")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[KenReay]

OK, but even so, a little code to adjust for the possibility that the first and/or last day may be incomplete would remove the need for a time consuming loop

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK