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?

--
__________________________________
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

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

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

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

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

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.