datediff

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alan Zanini - 23 May 2008 12:17 GMT

Hi, I Have to calculate the difference in hours between dates and times
ignoring weekends, holidays and the hours between 6 Pm till 9 Am.
Do any one know how could I do that

Reply to this Message

Ron2006 - 23 May 2008 13:27 GMT

1) Datediff("n",fromdate,todate) will tell you how many minutes in
total between the two dates.

2) datediff("d",fromdate,todate,7) will tall you how many Saturdays
between the two dates.

3) datediff("d",fromdate,todate,1) will tell you how many Sunday's
between the two dates

4) If you create a table of dates that represents the workdays being
taken off for a holiday (If holiday is on a saturday then put Friday's
date in the table). THEN
dcount ("*","holidayTable", "HolidayTableDate between #" & fromdate &
"# and #" & todate "#")

5) multiply the sum of items ( 2, 3, and 4) by 24 * 60 to get the
total number of minutes for saturdays, Sundays, and Holidays.

6) subtract item 5 from item 1 and then divide the answer by 60 to get
the number of hours between the two dates.

Ron

Reply to this Message

Alan Zanini - 23 May 2008 15:32 GMT

Thanks for that!
Do you have any idea how can I make Datediff( ) between these dates ignoring
the hours between 5 pm till 9am.?

Thanks

Reply to this Message

Ron2006 - 23 May 2008 20:33 GMT

Believe it or not, as I was writing my answer, I was thinking of "has
he thought of that aspect?" But the actual hours that were to be not
counted need to be know.

One possible solution, and you will have to test it to see if it is
right.

7) use Datediff("d",fromdate,todate) to get the number of days between
the two

8) Multiply (answer 7 less answer 2 and answer 3 and answer 4) by 16
* 60

9) subtract answer 8 from answer 6 BEFORE deviding by 60

In a nutshell you want the total number of minutes between from
timestamp and to timestamp.
Then either of the following:
A subtract 480 minutes (8 hours times 60 minutes) for weekend and
holiday days and then subract 16 hours (960 minutes for every day
between the two timestamps

OR

B) subtract 24 hours times 60 minutes for weekends and holidays and
then 16 hours times 60 minutes for every workday between the two
timestamps

Given that answer then divide it by 60 to get the work hours.

Ron

Reply to this Message