423,311 Members | 1,224 Online
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

 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

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?

10 Replies

 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

 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

 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

 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

 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

 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

 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

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

 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

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