Dates Minus weekends

[mdpres]

I wanted a calc to let a user know they had 5 business days to return a book to the library.

This is my work around.

Hope it helps someone,

Dean

[LaRetta]

Hi Dean,

If you change your Date In to:

Case(DayofWeek(Date Out) = 2,Date Out + 4,

DayofWeek(Date Out) = 3,Date Out + 6,

DayofWeek(Date Out) = 4,Date Out + 6,

DayofWeek(Date Out) = 5,Date Out + 6,

DayofWeek(Date Out) = 6,Date Out + 6)

... then you can drop the other two calcs. DayOfWeek is a function already and so is DayName!

And you can actually even shorten it further with:

Case(DayofWeek(Date Out) = 2,Date Out + 4,

DayofWeek(Date Out) > 2 and (DayofWeek(Date Out) < 7), Date Out + 6)

Cheers,

LaRetta

[CobaltSky]

Or, you could do exactly the same thing with:

(DayofWeek(Date Out) > 2) * 2 + Date Out + 4

- which will calculate in around a quarter of the time of the expression suggested in the previous post...

[mdpres]

Ray,

Thank you for the calc, make life easier.

Dean

[mdpres]

Thank you LaRetta & Ray.

Dean

[jasonwood]

CobaltSky said:

(DayofWeek(Date Out) > 2) * 2 + Date Out + 4

Could someone tell me what's going on here?

Here's what I'm looking to do...

Each of my suppliers has a standard lead time (stored in their file) measured in business days. So when I place an order (there is a script), I want to set the ETA for the order to the current date plus the standard lead time in business days.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

[Vaughan]

You'll also need a table with public holidays as well, to be 100% accurate.

I seem to remember Bob Weaver posting something a sample file that did like this.

[jasonwood]

Ah yes, found it...

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=81682&page=0&view=collapsed&sb=5&o=all&vc=1

Pretty cool, but I think it's overkill for my situation. I'm not concerned at all about holidays - I may never even look at the date in this field, it's just a nice thing to have.

Can I modify Ray's formula so it works when the lead time is variable?

Thanks.

[Vaughan]

April for us here in Australia has been a great month for public holidays.

Easter gave us Good Friday 9 and Easter Monday 12, plus we have ANZAC Day on Monday 26. So out of the four full weeks in April only one has a full five working days.

[CobaltSky]

Hi Jason,

If you are using v7 and you want:

- to deal with a variable number of days,

- to enter the total number of days to be inclusive (ie to include the start date)

- to have internal error handling if the start date entered is not a weekday

and you are happy to ignore public holidays (ie to count them as work days), then I suggest you use:

Let(

[X = (Days - 1);

Y = DayOfWeek(StartDate);

Z = StartDate + If(Mod(5 + Y; 6); 0; Int((5 + Y) / 6))];

Z + Int(X / 5) * 7 + Mod(X; 5) + If((Mod(X; 5) + DayOfWeek(Z)) > 6; 2)

)

...where 'StartDate' is a date field and 'Days' is a number field that supplies your variable lead time.

FileMaker Version: Dev 7