Removing a fixed amount of time from a formula that is calculating working hours

[Gremlin_za]

Hi All,

Thanks to this forum and the great users here i am currently using a formula to calculate the time lapsed between the time a ticket was opened to the time it was closed.

the formula is below.

=IF(J40="","",(NETWORKDAYS(H40,J40,Holidays!$A$1:$A$14)-1)*("18:00"-"07:00")+MOD(J40,1)-MOD(H40,1))

I am using network days to remove weekends and a set of listed public holidays, i have also set the non working hours. this formula is working wonders.

However i have now run into a snag, and my understanding of writing forumla's is fairly rusty at this stage to say the least.

I have now got an incident where we have a ticket that was opened, closed, re-opened and then closed, i can use the formula to calculate the time between the first time it was opened and the last time the ticket was closed, however there are approximately 44 hours of dead time where the ticket was not in my view and was technically closed which i would like to remove from the final calculation.

Would anyone be able to assist in adjusting the formula above to be able to manually tell the formula to remove 44 hours from the total without having to create multiple new cells

Kind Regards,

Gremlin

[Pepe Le Mokko]

Hi
do you mean

Please Login or Register  to view this content.

?

[Gremlin_za]

Thanks very much, thats exactly what i needed, was looking at it in far too complex a matter