workdays question/help

[Kim K]

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!

 

[Mike H]

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.

 

[Fred Smith]

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

 

[T. Valko]

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)

 

[Kim K]

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!

 

[Fred Smith]

Glad you got it working finally.

Regards,
Fred

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!