Please help me to create a formula for my database....

  • Thread starter Thread starter luisfer
  • Start date Start date
L

luisfer

Hello all, I have an excel sheet with over 480 entries (rows), I have
columns A=Out Date, B=Out time, C=In Date, D=In Time, I have the date
formated as DD/MM/YYYY and Time as 00:00:00 to 23:59:59.

I would like to make a formula that could give me the "real time" tha
the item I borrowed was out in use, for example if someone got
screwdriver on 24/07/2004 07:00:00 and returned on 25/07/2004 15:30:0
I would like the amount of time that screwdriver was out. If th
screwdriver was out for the weekend , I dont want the weekend hours t
be included.

is it possible? Thanks in advance peopl
 
You haven't said how you want to treat overnight hours.

=NETWORKDAYS(A1,C1)-1-(D1<B1)

is the number of full days, and

=IF(D1<B1,1+D1-B1,D1-B1)

(formatted as time) is the number of hours, assuming that out at 4:30pm
and back at 7:30am should be considered 15 hours.

Jerry
 
Thanks for your prompt response, i think I´m a little bit lost, Th
formulas didn´t work in my worksheet (#name?).

Let me tell you what I have done. I entered the following formula t
have the total hours of the item in use when in returned the same day

=IF((c1-a1=0),(d1-b1),0)

With this formula if the "in date" is different than the "out date" th
returned value is 0

Let me explain a little bit more:

The day has 24 hrs, but we only work 2 shifts, our working hours ar
19hrs per day (from 7:00 am to 02:00am). If someone takes a screwdrive
today 24/07/2004 at 13:30 and he returns it tomorrow 25/07/2004 at 8:3
then the amount of time the screwdriver was out is 12:30hrs of today
2:00hrs of tomorrow = 14:30 total.


What will be the formula to get this same result using this same data?

Thanks in advanc
 
Hi,

In your original post you say: "I dont want the weekend
hours to be included".

But in your example: "today 24/07/2004 at 13:30 and he
returns it tomorrow 25/07/2004 at 8:30", you are using
weekend values.

Explain please.

Biff
 
NETWORKDAYS() is a function from the Analysis ToolPak (as documented in
Help for NETWORKDAYS()). Since you get #NAME?, you presumably do not
have ATP loaded. Click Tools|Add-Ins and put a check by Analysis
ToolPak. You may need your installation CD.

Jerry
 
I shouldn´t use those dates, ignore them and lets have 24/07/200
changed to 27/07/2004 and 25/07/2004 to 28/07/2004.


Thanks

I´ll try to install de tool pack, I will return as soon as I finis
 
Back
Top