423,311 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,311 IT Pros & Developers. It's quick & easy.

working hours calculation including weekends between 10:00 to 18:00 hrs only

bsmeena7005
P: 6
Hi
help me to calculate the working hours between 10 to 18 hrs between two dates
including the weekends also no braek or lunch time

Example :-

start date #01-08-2009 08:45:23#
end date #03-08-2009 16:10:15#

plz. help me to provide the function for the above


thanks in advance
Aug 19 '09 #1

✓ answered by NeoPa

The trick here, is to design a function which returns the number of valid hours from a specific point in time. A point that you know will make calculation easiest. I suggest midnight on a Monday morning near the base-point of the date system in VBA 31/12/1899). As the way Date/Time values are stored is numerically as a count of days, this should be relatively straightforward. Work out how many weeks from this point, which accrues that many times the weekly value, then calculate the number of days (with a max of 5), then the valid hours into the day. This function should then return a value of the number of hours from that starting base-point.

If you pass both of the date/times into this function then you simply need to subtract one resultant value from the other.

Does this all make sense?

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 30,996
Where and how are the dates stored and referred to?
And where do you want the calculation to occur?
Aug 19 '09 #2

bsmeena7005
P: 6
I am using Access 2003 database and that function I will use in the query.

I am having start date as S_date
and end date as R_date in my database table.

plz. help me
Aug 20 '09 #3

NeoPa
Expert Mod 15k+
P: 30,996
Not forgotten, just quite busy atm.

Will get to when I can, but this isn't a 5 minute job.
Aug 21 '09 #4

bsmeena7005
P: 6
I know I am frustrated from last two months with this problem
plz. help me as and when u get time
Aug 21 '09 #5

NeoPa
Expert Mod 15k+
P: 30,996
The trick here, is to design a function which returns the number of valid hours from a specific point in time. A point that you know will make calculation easiest. I suggest midnight on a Monday morning near the base-point of the date system in VBA 31/12/1899). As the way Date/Time values are stored is numerically as a count of days, this should be relatively straightforward. Work out how many weeks from this point, which accrues that many times the weekly value, then calculate the number of days (with a max of 5), then the valid hours into the day. This function should then return a value of the number of hours from that starting base-point.

If you pass both of the date/times into this function then you simply need to subtract one resultant value from the other.

Does this all make sense?
Aug 21 '09 #6

bsmeena7005
P: 6
I am very much begineer to the access vba function plz. provide a easy way I am only doing the database queries with the help of access No function I have made
plz. help me in a easy way
I will be very much thankfull to u
thank you
Aug 22 '09 #7

NeoPa
Expert Mod 15k+
P: 30,996
I am not making this complicated. It started that way. If you want easy solutions you need to stop asking complicated questions.

If you're serious about wanting this you need to making some attempt at the code. I would rather not spoon-feed you the code, but I would be happy to help you in your attempts.
Aug 23 '09 #8

bsmeena7005
P: 6
Thank you sir the problem resolved and working very effectively
Sep 2 '09 #9

NeoPa
Expert Mod 15k+
P: 30,996
I'm very pleased to hear it. I hope I was some help in the process.
Sep 2 '09 #10

bsmeena7005
P: 6
Help is totally yours thanks again
Sep 12 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.