How long have we had this product?

[evan_draughon]

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.

[JBeaucaire]

Maybe:

=IF(NETWORKDAYS(A2,B2)<2,MOD(B2,1)-MOD(A2,1),($E$1-MOD(A2,1)+MOD(B2,1)-$D$1)+((NETWORKDAYS(A2,B2)-2)*10/24))

http://screencast.com/t/dxlkRsKXL

[evan_draughon]

Let me give it a try, thanks JBeaucaire

[evan_draughon]

It is still off somehow. Here is the sample page that I am working with.Working Formula.xls

[JBeaucaire]

Try:

=NETWORKDAYS(A1,B1)*(($E$1-$D$1))+($E$1-(MOD(A1,1)))+(MOD(B1,1)-$D$1)

[evan_draughon]

Still off. The last date in this file is like a week long and it is only showing 17 hours. Ill post the updated form so you can see what it shows. Working Formula.xls

[JBeaucaire]

1) You didn't put in my suggested formula, makes me sad.

Try this one instead: =((NETWORKDAYS(A1,B1)-2)*(($E$1-$D$1)))+($E$1-(MOD(A1,1)))+(MOD(B1,1)-$D$1)

2) You need to correct the formatting of the cells so they will show more than 24 hours. The cell format should be:

Custom: [h]:mm:ss

[evan_draughon]

I had the formula in, I just linked the wrong file. Everything seems to be working well. There are still a few hiccups, but for the most part I have been able to figure them out. Thank you for your help again.

[JBeaucaire]

Always glad to assist.
If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.