NETWORKDAYS IF date falls on a holiday, otherwise...

so, I've run into a bit of a pickle. The formula below works very well, except where the start date falls on a holiday. My formula excludes holidays from the equation, however there are rare cases where we are authorized to work a holiday, and if the start date is on a holiday it comes up with a negative value. What I would like is possibly an IF statement to look at the date range for my holidays (HLDY), and if the start date falls on one of these holidays, then start the clock on the next workday (after 6am(BusinessStart))....however, if the start and close date both fall on the same holiday in the range(HLDY), then just display 0, otherwise use the NETWORKDAYS forumla as I have listed below.

If the ticket is opened on a holiday, no time is charged toward the ticket. The clock would actually start on the next workday.

I hope that's not too confusing.

Please Login or Register  to view this content.

My formula determines if the ticket is Open or closed by seeing if the word "Closed" is in Column E. If Closed, then it displays "Closed" + the time the ticket was open from subtracting the start date (Column G) from the Closed date (Column H), other wise if it's not closed then just display the time the ticket has currently been opened until now ('!'!$P$75).

Column E = "Closed" or some other text
Column G = Ticket start date formatted as m/dd/yyyy h:mm
Column H = Ticket close date formatted as m/dd/yyyy h:mm

'!'!$P$75 =NOW() formatted as m/dd/yyyy h:mm AM/PM

BussinessStart = 6:00 formatted as h:mm
BusinessEnd = 18:00 formatted as h:mm

HLDY = 'Main'!$R$9:$R$19

I've tried several different things to get this thing working but I can't seem to get it right.

Btw, if you find any errors in my formula, it's because I had to type it freehand.

Any help is much appreciated.

Thanks,

Tim