Complex Network Days function & Time - Help please

A

Adam

Morning All,

I am attempting to work out the time in which a customer enquiries been open for. I have the below info:

A5 Call date
B5 Call Time
I5 Todays Date
J5 Time as at Now

I have the function: =((NETWORKDAYS(A5,I5)-2)*10)/24+TIME(17,30,0)-B5+J5-TIME(9,0,0)

I then have the cell formatted as "dd hh mm" and this returns, taking into account the staff working hours, the number of work hours the call has been open.

But for a day its taking 24 hours, is there anyway I can have a day as at 12 hours?

To explain further if a calls logged at 9am Monday and then closed at 10am Tuesday that should be classed as 1 day 1 hour but with the current formula it would be marked as 13 hours.

Any help please?
 
J

Jerry W. Lewis

Assuming your formula calculates the hours as you wish, why not just
build on that
=INT(formula*2) &" days"
=MOD(24*formula,12) &" hours"

Note that if B5, I5, and J5 are held constant, but A5 is reduced by 1,
that will add only 10 hours, not 12 to your time formula.

Jerry
 
Top