Hello All,
I am in need of calculating the number of hours between two days excluding the non office ours and weekends and holidays.Could any one help me on this.Thanks in advance.
My work start time : 7 AM
My work end time : 7 PM
weekend :Saturday and Sunday
User can start work at any time (ex . 6:45 AM) and finish at any time say 7:45 PM but the calculation only should happen between 7 AM and 7 PM.
please see attachment, if you were looking for a different solution please explain it
Thank you
I couldnt get what you have given in the attachement.
In the attachement am unable to find any data related to time difference calculation.
Actually what i need is time difference between two dates in days and hours ,it excluding holidays and non business hours .
Thanks for your time on this.
To understand better: say you enter 10/16/12 & the next cell 11/09/12 it should calculate how many days. please explain if that it was you want and where are the hours coming into.
Thank you
P.S. if you can attach a sheet it would help!
Hello
Please see the attachment
Please see attached file, i hope i understand what you want, please let me know if you need any changes
thank you
i think you need to use =networkdays() you can convert that to hours by *8
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
JFR2262's solution works for me.....
I have a standard formula for these, in this case it goes like this....
=NETWORKDAYS(A6,B6,F$11:F$20)*12-12+IF(NETWORKDAYS(B6,B6,F$11:F$20),MEDIAN(MOD(B6,1)*24,7,19),19)-MEDIAN(NETWORKDAYS(A6,A6,F$11:F$20)*MOD(A6,1)*24,7,19)
That will give you hours as a decimal, e.g. 27.5, if you want that as 27:30 then put parentheses round the formula and divide by 24
Audere est facere
Re: Calculating business hour between two days excluding holidays and weekends
Hello JFR2262,
Thank you so much for your help.Its great !!!
Hello daddylonglegs,
Thank you so much.Your formula also works well
HiOriginally Posted by christophertpj
Instead of 7 to 7, I would like to change as 9:30 to 5:30 And also I want to consider 4 hours in Saturday...I.e fro 9:30 to 1:30.
Could you please help me to modify the formula and send the attachment
Thanks
Shankar
Last edited by alansidman; 06-13-2014 at 09:00 AM.
@Sankar_tsn
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks