Alternative formula to exclude holiday calculation

[Cam]

Hello,

I have this formula and return the date in (date/time format) that exclude
weekend and holiday. But for whatever reasons (don't know) it is not
excluding holiday in the calculation. Wondering is there a different formula
to use to excluding those holiday which is a list of date on a separate sheet.

=WORKDAY($M$1,INT(K3),Holiday!$A$2:$A$109)+MOD(K3,1)

where M1 is the start date
K3 is number of days required ex: 2.015 days.
A2:A109 is a list of holiday date.

Thanks

 

[Bob Phillips]

It is working fine here. Is it giving you the correct date disregarding
holidays, or some error?

 

[JE McGimpsey]

Are the dates in your list entered as true XL dates?

If they're entered as Text they'll be ignored.

 

[Cam]

Thanks for the reply Bob,
It is not picking up the holidays when it returned the date.
For example, my holiday listed date is 12/24/08 thru 1/1/09, it is not
ignoring them, instead returned the date that fall in these date range. It
should skip the date.

 

[Cam]

JE,

Yes, they are entered and formated as date field.

 

[Ron Rosenfeld]

Hello,

I have this formula and return the date in (date/time format) that exclude
weekend and holiday. But for whatever reasons (don't know) it is not
excluding holiday in the calculation. Wondering is there a different formula
to use to excluding those holiday which is a list of date on a separate sheet.

=WORKDAY($M$1,INT(K3),Holiday!$A$2:$A$109)+MOD(K3,1)

where M1 is the start date
K3 is number of days required ex: 2.015 days.
A2:A109 is a list of holiday date.

Thanks

Your formula should work, so there is some issue with your data or your cell
references.

What is the actual data?
Does =ISTEXT(various_cell_refs) return FALSE in all cases?
What is the actual result?
What is the expected result?
--ron

 

[JE McGimpsey]

Then I can't think of any reason they'd be ignored (I'd double check,
though - does =ISNUMBER(A2) return TRUE?).

I suppose another possibility is that if your dates are formatted as
mm/dd/yy, then

12/31/08

could possibly be the value for

12/31/1908

which, of course, would be ignored.