How do you calculate the hours between two dates excluding the weekends?
Hi, how do you calculate the hours between two dates excluding the weekends?
A1: 06/06/2013 12:00 AM
B1: 06/12/2013 12:00 AM
I get 144:00 as difference using the formula =IF(B1="","",(B1-A1))
However June 8 and June 9 are weekends here in our place, so the result needs to be 96:00
Last edited by Charoum; 08-02-2013 at 11:58 PM.
=NETWORKDAYS(A1,B1-0.001)*24
If you leave out the -.001 (or any miniscule, irrelevant fraction of a day) the defined function will include the the last day as a whole day, even though the time is exactly the same as the beginning time.
Please advise if you intend to use times other than 12 midnight...
Last edited by jhren; 08-03-2013 at 08:54 AM.
Yes, I'll also be using other times aside from 12 midnight...will the formula also be applicable to:
A1: 06/06/2013 1:30 AM
B1: 06/12/2013 1:30 AM
oh by the way, the value needs to be in this format [h]:mm
so it's 96:00
* I tried your formula, it resulted to -48... please advise
Last edited by Charoum; 08-03-2013 at 07:30 AM.
Assuming that the dates won't be at weekends this version will use the times too
=NETWORKDAYS(A1,B1)-1+MOD(B1,1)-MOD(A1,1)
format result cell as [h]:mm
Audere est facere
Hi it works but I get ###### when A1 and B1 cells are blank, how do make it blank when there is nothing written on the A1 and B1 cells?
Thank you so much
You can make the formula apply only when both A1 and B1 are populated using this version
=IF(COUNT(A1,B1)=2,NETWORKDAYS(A1,B1)-1+MOD(B1,1)-MOD(A1,1),"")
What about this array formula. Formulas above are much more simpler and recommended to use.
=((SUM(IF(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(A2))))>=6,0,1))-1)+(A2-INT(A2)-(A1-INT(A1))))*24 Ctrl+Shift+Enter
Appreciate the help? CLICK *
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks