Calculating Work time by minutes

[mr moe]

HI Guys,
I came across an example from this forum, which I'm using but I think it got some bugs, I hope that someone can help. Attached is the file.

There is a modual for this, i'm trying to calculate the time between the Open Date and Close Date columns, it works for some dates but not for the other, please take a look at it. Go to Queries and run the query, thanks.

I'm trying to get the total minutes, there is a start shift and end shift, there is also a holiday table, this module also excludes saturday and sunday.

 

[mr moe]

Can someone fiqure it out?

 

[pdx_man]

Patience my friend ... some of us do have real jobs to attend to ...

No bug in the original program. You have not finished modifying it to work as minutes. The logic there is for 8 hour days and you want to do this in minutes ... Everywhere there is a reference to 8, you must now update this to 8 * 60, the number of minutes in the day. If you are going to use this, update the variable names. You can do a search and replace and have it updated in 5 seconds.

 

[mr moe]

it is working for some dates and not the others see bleow: Between 8:00:00 Am and 5:00:00 PM work days.

Open Date........................Close Date .......................Min........Results
6/29/2007 4:59:00 PM........ 7/2/2007 8:00:00 AM......... 1........GOOD
6/29/2007 4:59:00 PM........ 7/2/2007 8:10:00 AM......... 9........GOOD
6/29/2007 4:59:00 PM........ 7/2/2007 8:20:00 AM......... 9 ....... BAD...............should be 19 min
7/2/2007 4:00:00 PM.......... 7/3/2007 8:00:00 AM......... 60...... GOOD
7/2/2007 1:00:00 PM.......... 7/2/2007 5:00:00 PM......... 240..... GOOD
7/2/2007 3:00:00 PM.......... 7/3/2007 11:00:00 AM........ 128..... BAD...........should be 300 min

 

[RuralGuy]

Try this one.

 

[mr moe]

Try this one.

Thanks man, you are a Genius.

 

[RuralGuy]

You're welcome. Glad I could help.

 

[mr moe]

Oh thanks a lot, I have one issue though, i'm trying to resolve it but not yet, take a look please, it's the minutes with the "minus" sign, this happens when the open date and close date are outside the business hours. see attachment.

 

[mr moe]

Try this date if you don't mind, it should be 31 minutes, it's showing -439,

the open date is 5/30/2007 4:29:21 PM
the close date is 5/31/2007 1:10:00 AM

results showing -439
resutls should be 31

 

[RuralGuy]

OK. Give this a try.

 

[mr moe]

OMG Man you're so good. I owe you big time!