Alternative formula to exclude holiday calculation

  • Thread starter Thread starter Cam
  • Start date Start date
C

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
 
It is working fine here. Is it giving you the correct date disregarding
holidays, or some error?
 
Are the dates in your list entered as true XL dates?

If they're entered as Text they'll be ignored.
 
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.
 
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
Click to expand...

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
 
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.
 
Back
Top