Alternative formula to exclude holiday calculation
 

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

Alternative formula to exclude holiday calculation
 

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

--
__________________________________
HTH

Bob

"Cam" wrote in message
...
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

Alternative formula to exclude holiday calculation
 

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

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


In article ,
Cam wrote:

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

Alternative formula to exclude holiday calculation
 

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Cam" wrote in message
...
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

Alternative formula to exclude holiday calculation
 

JE,

Yes, they are entered and formated as date field.

"JE McGimpsey" wrote:

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

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


In article ,
Cam wrote:

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

Alternative formula to exclude holiday calculation
 

On Wed, 14 Jan 2009 08:34:07 -0800, Cam wrote:

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

Alternative formula to exclude holiday calculation
 

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.

In article ,
Cam wrote:

Yes, they are entered and formated as date field.