Subtracting Days from a Deadline

[Niet32]

Meant to put this in Define Fields.

OK. I looked through the posts and can't figure this one out.

I have one date, a Deadline date, and need to show the date 3 days prior from that date.

The problem is I can't include weekend days.

All the formulas I have seen are based off of a start and end date.

Even when I reference a field (end date) that takes the Deadline and subtracts 3 days it doesn't worked.

Thanks for any help. I am sure I am missing something.

Edited June 8, 2006 by Guest

[John Mark Osborne]

Here is a formula that will allow you to specify a Start Date and a number of days:

Start Date +

Div(Days; 5) * 7 +

Middle("12345012340123601256014560345623456"; (DayofWeek(Start Date) - 1) * 5 + Mod(Days; 5) + 1; 1)

You need to make sure the Start Date falls on a week day date.

The series of numbers included in the middle function represent the number of days you need to add to each day of the week in order to make it a week day. For example, the first 5 digits refer to Sunday (12345). Adding 1 day to Sunday makes it Monday, adding 2 days makes it Tuesday, etc. The next 5 digits refer to Monday (01234). Adding 0 days makes it Monday, adding 1 day makes it Tuesday, etc. The next 5 digits represent Wednesday (01236). The jump from three to six represents the weekend (i.e. 4, 5).

[Niet32]

What does Days refer to? Is that the 3 days prior? Should I put that in as a number?

Thanks

[comment]

Your question is not very clear. Do you want the last work day that is at least 3 days before the deadline, or do you want a date that is 3 work days before the deadline?

[Niet32]

I am looking for the date 3 work days prior to the deadline.

Thanks...

[comment]

If you are willing to hardcode the number -3 into the formula, you could use a simple mini-lookup like this:

Deadline - Middle ( "4555333" ; DayOfWeek ( Deadline ) ; 1 )

Very much like John Mark's formula above, it simply pre-computes the number of days you need to subtract, depending on the day-of-week of deadline (here the deadline is allowed to fall on the weekend).

For a more general formula, see here.

[John Mark Osborne]

Days refers to the number of days in the future. You can replace it with the number 3 if you like.

[Niet32]

Thanks for all your help!