I am trying to make a template for our staff that will easily calculate 50
school days from a given date. This is a state compliance issue for special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get 4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing me
what I am doing wrong will be greatly appreciated.

Thanks!

Ki,

I'm a bit confused by this question

1/1/2010 + 50 workdays (Excluding any holidays) is 12 March 2010

so with your date in a1 this formula formatted as a date

=WORKDAY(A1,50,B:B)

Returns the date + 50 days taking account of holidays in column B

I would suggest you shorten the range B:B to something realistic
=WORKDAY(A1,50,$B$1:$B$50)
This would allow 50 holidays which in the UK is more than enough
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Kim K" wrote:

I am trying to make a template for our staff that will easily calculate 50
school days from a given date. This is a state compliance issue for special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get 4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing me
what I am doing wrong will be greatly appreciated.

Thanks!

Two problems:

1. You can't use a full column for holidays in Workday. Give it the specific
range.
2. Your formula is calculating 50 days from *today*, not the date in A1.


Try:
=workday(a1,50,b1:b20)

Regards,
Fred

"Kim K" wrote in message
...
I am trying to make a template for our staff that will easily calculate 50
school days from a given date. This is a state compliance issue for
special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get
4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing me
what I am doing wrong will be greatly appreciated.

Thanks!

In A2 I use the formula =WORKDAY(TODAY(),50,B:B
and will get the same date no matter what date
I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in
2/12/2010 I get 4/26/2010. Obviously I am doing
something wrong.....

Shouldn't you be referring to cell A1 rather than TODAY()? Also, you can't
reference an entire column for the Holidays argument.

=WORKDAY(A1,50,B1:B10)

--
Biff
Microsoft Excel MVP


"Kim K" wrote in message
...
I am trying to make a template for our staff that will easily calculate 50
school days from a given date. This is a state compliance issue for
special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get
4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing me
what I am doing wrong will be greatly appreciated.

Thanks!

Hi Mike,

Yes this works now and thank you! The other posted formula was provided by
another user, so must have been some minunderstanding on my part in my
explaination.

Thanks everyone!

"Mike H" wrote:

Ki,

I'm a bit confused by this question

1/1/2010 + 50 workdays (Excluding any holidays) is 12 March 2010

so with your date in a1 this formula formatted as a date

=WORKDAY(A1,50,B:B)

Returns the date + 50 days taking account of holidays in column B

I would suggest you shorten the range B:B to something realistic
=WORKDAY(A1,50,$B$1:$B$50)
This would allow 50 holidays which in the UK is more than enough
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Kim K" wrote:

I am trying to make a template for our staff that will easily calculate 50
school days from a given date. This is a state compliance issue for special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get 4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing me
what I am doing wrong will be greatly appreciated.

Thanks!

Glad you got it working finally.

Regards,
Fred

"Kim K" wrote in message
...
Hi Mike,

Yes this works now and thank you! The other posted formula was provided
by
another user, so must have been some minunderstanding on my part in my
explaination.

Thanks everyone!

"Mike H" wrote:

Ki,

I'm a bit confused by this question

1/1/2010 + 50 workdays (Excluding any holidays) is 12 March 2010

so with your date in a1 this formula formatted as a date

=WORKDAY(A1,50,B:B)

Returns the date + 50 days taking account of holidays in column B

I would suggest you shorten the range B:B to something realistic
=WORKDAY(A1,50,$B$1:$B$50)
This would allow 50 holidays which in the UK is more than enough
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Kim K" wrote:

I am trying to make a template for our staff that will easily calculate
50
school days from a given date. This is a state compliance issue for
special
education, I
need to be able to have the secretaries calculate this date for student
evals.

I have formatted the sheet for dates. In A1 I type in the date I want
to
figure 50 days from, in column B I have typed in ALL school days off.

In A2 I use the formula =WORKDAY(TODAY(),50,B:B and will get the same
date
no matter what date I enter into A1, ie if I enter in 1/1/2010, the
return
date for 50 days will be 4/26/2010, if I type in 2/12/2010 I get
4/26/2010.
Obviously I am doing something wrong.....

The ultimate goal is a template for all surrounding districts that the
secretaries can easily use to calculate the 50 days - any help showing
me
what I am doing wrong will be greatly appreciated.

Thanks!