Projecting project completion dates using # of hours

[airwitte]

Don't know if this is possible in Access, but here goes:



I am trying to develop a database that will allow our warehouse to enter an order we receive from a customer and enter the estimated # of hours it will take to complete the work. The warehouse staff will also enter a start date and time into the record.

So, here is an example: Warehouse enters a record into Access for Customer A. They enter a startDate/time of 12/2/10 at 3pm (basically, the day/time it was received by us), and then they enter how many hours they THINK the project will take (let's assume 43 hours to complete this order). I need to have Access figure out the date that the project is estimated to complete. Keep in mind, I need to exclude company holiday dates, and count # of hours the warehouse works every day. Weekdays are 10 hours a day, Saturdays are 5, and Sundays are 0.

[ajetrumpet]

check out DATEDIFF(). also check out the 'EDATEDIFF()' functions here: http://www.ajenterprisesonline.com/_functions/ (for holiday exclusion possibilities).

the easiest answer to this would start with a variation of datediff() using days, and multiplying the result by 10.

[William McKinley]

Wouldn't DateAdd() work well for this?

[ajetrumpet]

Wouldn't DateAdd() work well for this?

Very good. Looked right past it. thank you William.

[airwitte]

Very good. Looked right past it. thank you William.

How would that help? I have only one date/time (startdate/time), and I don't know when the end date is. And I still need to account for weekend days having different hours from weekdays...

[ajetrumpet]

How would that help? I have only one date/time (startdate/time), and I don't know when the end date is. And I still need to account for weekend days having different hours from weekdays...

this is custom, airwitte. You cannot do this whole task with built-in functions by Access. It's not possible.

example though - if the project starts today, and the user thinks it takes 40 hours to complete, the simple answer for an end date would be:

dateadd("h", 40, date())

but, as you said, there are variations, so you have to write code to cover the possibilites, and combine that code with the dateadd() function, as above, to make it work. Understand?

the page I gave you earlier can be used as a starting point, as the exclusions you're looking for are demonstrated quite clearly there.

[William McKinley]

Not sure if you figured this out yet, but I took a shot at it. My function might be a little bit(or extremely) excessive, but I need some practice so what the heck. I didn't get around to adding in whether it discerns between weekdays/weekends, but it might get something going.

It assumes an 8:00AM-6:00PM Work day.

Expr1: IIf(([ProcessTime]+Hour([StartDate]))<=18,DateAdd("h",[ProcessTime],[StartDate]),DateAdd("h",8+(((([ProcessTime]-(18-Hour([StartDate])))/10)-Int(([ProcessTime]-(18-Hour([StartDate])))/10))*10),DateAdd("d",1+(Int(([ProcessTime]-(18-Hour([StartDate])))/10)),DateAdd("h",-1*(DatePart("h",[StartDate])),[StartDate]))))