Problem with DateDiff (1 Viewer)

Morning

Pretty new to all this, so forgive my ignorance but i'm having problems with DateDiff. I have a table with 2 dates in it, and I want to work out how many working days (or working hours if possible) are between the 2 dates


SELECT DateDiff("d",[Date Opened]+[Date Closed])
FROM Dashboard;

I want it to print it the number of working days/ hours in a new column in the Dashboard table if possible.

Any ideas?

Thanks

Hi,

The correct syntax to use to return the number of days between two dates is:
DateDiff("d", [Date Opened], [Date Closed])

To make both dates inclusive, you need to add +1 to it:
DateDiff("d", [Date Opened], [Date Closed]) +1

Another equivalent expression is:
[Date Closed] - [Date Opened] +1


To arrive at the number of working days, you can deduct from it the number of Saturdays and Sundays like this:-
[Date Closed] - [Date Opened] +1
-(DateDiff("ww",[Date Opened],[Date Closed],7) -(Weekday([Date Opened])=7))
-(DateDiff("ww",[Date Opened],[Date Closed],1) -(Weekday([Date Opened])=1))

Note: If [Date Opened] is never a Saturday or a Sunday, you can remove the parts -(Weekday([Date Opened])=7) and -(Weekday([Date Opened])=1) to make the expression shorter and more efficient.


If you need to exclude holidays, too, you can store each HolidayDate in a table tblHoliday and deduct the number of holiday days from it like this:-
[Date Closed] - [Date Opened] +1
-(DateDiff("ww",[Date Opened],[Date Closed],7) -(Weekday([Date Opened])=7))
-(DateDiff("ww",[Date Opened],[Date Closed],1) -(Weekday([Date Opened])=1))
-(Select Count(*) from [tblHolidays] where [HolidayDate] between [Date Opened] and [Date Closed])

This expression should return the number of working days excluding Saturdays, Sundays and holidays.


You can multiply it by the number of working hours per day to get the total number of hours.

Hope this helps.
.

Thanks very much for the reply, it seems to work but what I need is it to add in an extra column showing me the amount of time it took to close.

Any ideas how i do that??

Thanks


Kevin

Assuming there are 8 working hours per day, you can add a numeric field called Hours in the Dashboard table and then run an update query based on the previous expression to update the Hours field with the number of total hours.

Update query:-

UPDATE Dashboard SET Hours = ([Date Closed]-[Date Opened]+1
-(DateDiff("ww",[Date Opened],[Date Closed],7)-(Weekday([Date Opened])=7))
-(DateDiff("ww",[Date Opened],[Date Closed],1)-(Weekday([Date Opened])=1))
-DCount("*","tblHolidays","[HolidayDate] between DateValue('" & [Date Opened] & "') and DateValue('" & [Date Closed] & "')")) *8
WHERE [Hours] Is Null OR [Hours]=0;


(Note: As we can't use a subquery in an update query, we have to change the subquery into a DCount() expression.)


However, since a select query functions just the same as a table, we normally do not store calculated results in tables.
.

Works like a dream, Ta muchly!