how to calulate workday funtion

Hi

if you can help me

I am trying to find a date 3day out from today,but that date can't be weekend and also if that day fall on the sat make is friday and if fall on sunday make it monday, also i have 5days holiday. excel 2003

thank you

Try this:

=IF(WEEKDAY(TODAY()+3)=1,(TODAY()+4),IF(WEEKDAY(TODAY()+3)=7,(TODAY()+5),(TODAY()+3)))

Alan

Asssume your list of holidays in date format are listed in cells c1:c11, then apply this formula in cell A1.


=IF(AND(OR(TODAY()+3=C1,TODAY()+3=C2,TODAY()+3=C3,TODAY()+3=C4,TODAY()+3=C5,TODAY()+3=C6,TODAY()+3=C7,TODAY()+3=C8,TODAY()+3=C9,TODAY()+3=C10,TODAY()+3=C11),WEEKDAY(TODAY()+3,1)=6),TODAY()+5,IF(OR(TODAY()+3=C1,TODAY()+3=C2,TODAY()+3=C3,TODAY()+3=C4,TODAY()+3=C5,TODAY()+3=C6,TODAY()+3=C7,TODAY()+3=C8,TODAY()+3=C9,TODAY()+3=C10,TODAY()+3=C11),TODAY()+4,IF(WEEKDAY(TODAY()+3,1)=1,(TODAY()+4),IF(WEEKDAY(TODAY()+3,1)=7,(TODAY()+5),(TODAY()+3)))))


I am sure that this could be applied in a cleaner manner by using some VBA, but this works. Keep in mind that you will have to update the dates in column C as they are static. If you add more dates, then you will need to incorporate those values into the If(And(or statement.

Alan

If the holidays are listed in column C starting at C1, then add this in D1 to indicate how far to "add" based on the weekday of the holiday:
=CHOOSE(WEEKDAY(C1,2),1,1,1,1,-1,2,1)

Copy that down, now the name that range of cells HolidayADD, and name the holidays in column C Holiday.

Now enter a start date in H2, any date.

Now this formula will give you the END date 3 days from now, skipping holidays, then adjusting backwards for Saturday or forward for Sunday:

=IF(ISNUMBER(MATCH((H2+3)+CHOOSE(WEEKDAY(H2+3,2),,,,,,-1,1), Holiday, 0)), (H2+3)+CHOOSE(WEEKDAY(H2+3,2),,,,,,-1, 1) + VLOOKUP((H2+3)+CHOOSE(WEEKDAY(H2+3,2),,,,,,-1,1), HolidayADD, 2, 0), (H2+3)+CHOOSE(WEEKDAY(H2+3,2),,,,,,-1,1))

That's big and ugly, but works.

You could create a named formula out of that, click on I2, then Insert > Name > Define and create a name called DatePlus3 and enter that formula above as the source.

Now the formula you put in I2 would be simply:

=DatePlus3

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

@JB, not sure that's generating the correct results... using the logic within the file (start inclusive in 3 days if workday) shouldn't J2 be 19th Jan given 17th is a PH ?

An alternative, using JBs sample file (with Holiday range)

=SMALL(IF((WEEKDAY(ROW(INDIRECT($H2&":"&$H2+10)),2)<6)*ISNA(MATCH(ROW(INDIRECT($H2&":"&$H2+10)),Holiday,0)),ROW(INDIRECT($H2&":"&$H2+10))),3)
confirmed with CTRL + SHIFT + ENTER (alone will not suffice)

The above is not efficient and Volatile, however, it's pretty robust and requires only start date and holiday date range.

The above assumes per JB's sample that start date is inclusive where work day.
If the start date should always be excluded - eg for 14th Jan result should in fact be 20th Jan then append the formula accordingly:

=SMALL(IF((WEEKDAY(ROW(INDIRECT($H2+1&":"&$H2+10)),2)<6)*ISNA(MATCH(ROW(INDIRECT($H2+1&":"&$H2+10)),Holiday,0)),ROW(INDIRECT($H2+1&":"&$H2+10))),3)
confirmed with CTRL + SHIFT + ENTER (alone will not suffice)

You may choose to make reference to 10 a multiplier based on workday requirement eg:

+10 becomes $A$1*7

where A1 holds additional days - this keeps the number of rows being processed reasonable (changing also final reference to 3 to $A$1)


EDIT:

should add, I have assumed (like the others) from thread title that we are trying to replicate the ATP WORKDAY function (to avoid need for ATP install on XL2003)

DO, I read the OPs need to be more literal.... add 3 days to the beginning date, then adjust forward/backward to get off the weekends... and if the result then lands on a holiday, move forward to the next non-weekend day.

Ah, I see.

Adapting the earlier example for the same (for sake of consistency)

=MIN(IF((WEEKDAY(ROW(INDIRECT($H2+3&":"&$H2+10)),2)<6)*ISNA(MATCH(ROW(INDIRECT($H2+3&":"&$H2+10)),Holiday,0)),ROW(INDIRECT($H2+3&":"&$H2+10))))
confirmed with CTRL + SHIFT + ENTER (alone will not suffice)

above assumes start is excluded from the calcs - ie K3 should be 10th Jan rather than 7th given start of 5th.