Smart date difference timer

[FishKiller]

StopWatch – Can anyone lend a hand with this problem?

Task: Create a timer type value which will indicate total elapsed time with certain fixed exclusions and one or more additional exclusions for Holidays. The timer needs to be smart enough to automatically advance the time if the time falls outside the allowed time.

ENVIROMENT: I have one SQL database with two tables in it. The name of the database is “PROID”. The name of the two fields are “APPTMSTR” and “WSSSTATS”. The two dates to be used by the timer are coming from the following fields“APPTSDATE” AND “WSSSTOPDATE”

FIXED EXCLUSIONS:
MONDAY 12:00AM – 6:59AM & 6:01PM – 11:59PM (7:00AM ~ 6:00 PM – working timer hours)

TUESDAY 12:00AM – 6:59AM & 6:01PM – 11:59PM (7:00AM ~ 6:00 PM – working timer hours)

WEDNESDAY 12:00AM – 6:59AM & 6:01PM – 11:59PM (7:00AM ~ 6:00 PM – working timer hours)

THURSDAY 12:00AM – 6:59AM & 6:01PM – 11:59PM (7:00AM ~ 6:00 PM – working timer hours)

FRIDAY 12:00AM – 6:59AM & 6:01PM – 11:59PM (7:00AM ~ 6:00 PM – working timer hours)

SATURDAY 12:00AM ~ 11:59PM
SUNDAY 12:00AM ~ 11:59PM

DYNAMIC EXCLUSIONS:
HOLIDAY1 YYYY/MM/DD HH:MM 12:00AM ~ 11:59PM

EXAMPLE:

APPTMSTR.APPTSDATE = 11DEC03 6:30PM
WSSSTATS.WSSSTOPDATE = 22DEC03 5:30PM

TIMER = 76.5 HOURS

 

[Reebo99]

I've e-mailed a response to you....

Reebo
UK

"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former."
- Albert Einstein (1879-1955)

 

[dgillz]

Ken hamady has a formula to determine the number of work days between 2 dates, excluding holidays. This formula can be easilty adapted to what you want to do. Here is the link:

http://www.kenhamady.com/form01.html

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[FishKiller]

Thank you for the information this helps out greatly!!! However it still leaves a hole in the formula. If the start time is on a weekday that is after the designated work hours how can the start time be advanced to the next available start date and time?

Example:
Normal Hours = Monday ~ Friday 0700 ~ 1800
Start date = Thursday 19:05
Advanced time = Friday 07:00