So I was given the task of finding out how many business hours we have had products in. I am average with Excel but no where near where I probably need to be to make the right formula. Hoping you guys can help me out.
Our current database is set up like this
A1 - Start Time 2/19/2013 8:42:30 PM
B1 - End Time 2/21/2013 12:20:26 PM
C1 - Total Time 39:37:56 (Retrived with the Formula =B1-A1)
The problem with this is that we are only opened from 7am-7pm Monday through Friday. The current formula takes in account weekends and 24 hour days.
So, I did a bit of searching and found a formula that is close to what I want.
=WORKDAY(A1,CEILING((B1+MOD(A1,1)-D$1)/(E$1-D$1),1)-1)+MOD(A1,1)+B1-CEILING(MOD(A1,1)+B1-D$1,E$1-D$1)+E$1-D$1
A1 - Is my start time
B1 - Is my end time
C1 - Should display total time we had product
D1 - Time we open
E1 - Time we close
It seems to have an error with certain issues. For example, one of the ones that wont work is
A1 - 2/22/2013 18:24
B1 - 2/26/2013 13:11
D1 - 7:00:00
E1- 19:00:00
My C1 cell shows 7:36:17 which cannot be right because the time in which we had the product covers a full Monday, which means we should have to add another 12 hours to the C1 total.
Not sure why my formula is messing up on larger dates and not the smaller ones. Again, any help would be appreciated. Thanks.
Bookmarks