9 Replies Latest reply on Apr 9, 2017 9:25 AM by Jaymo

# Calculating Number of Workdays including First Day

I have seen in other discussions how to calculate the next delivery day using:

StartDate + Int ( WorkDays / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek ( StartDate ) - 1 ) * 5 + Mod ( WorkDays ; 5 ) + 1 ; 1 )

But in my case I need a function which includes the Start Date as well as the End Date. This is for calculating Class dates.

In other words, if a class starts on a Monday, May 8th,  and runs for 10 days, the calculation shows it ending on Friday, May 26th, using a 5 days on and two days off

When trying the above calculation it shows the following Monday as the End Date. Which would be correct if I didn't need to include the Start Date as the first day.

How do I change the above calculation so it includes the Start Date? I've tried a bunch of things but just can't get it to work.

• ###### 1. Re: Calculating Number of Workdays including First Day

Are you sure that you want to use that formula? What happens if there is a holiday in that date interval?

There is another option that works from a table of "class dates" so that managing holidays is simply a data entry task and does not require redefining a calculation. While some use such a table in combination with a formula like what you've posted, I prefer to treat weekends as holidays and just work completely from the set of class date records in the table as that produces a simpler calculation.

• ###### 2. Re: Calculating Number of Workdays including First Day

Div((End Date - Start Date); 7) * 5 +

Mod(End Date - Start Date; 7) -

Case(DayofWeek(End Date) < DayofWeek(Start Date); 2; 0)

• ###### 3. Re: Calculating Number of Workdays including First Day

I'm not sure I completely understand, could you show me an example please?

• ###### 4. Re: Calculating Number of Workdays including First Day

Thanks Jaymo,

I think I explained that wrong. What I meant was if someone puts in Monday May 8th, and then in the next field puts in 10 days, the End Date field should calculate Friday, May 26th.

• ###### 5. Re: Calculating Number of Workdays including First Day

May 26th or May 19th?

• ###### 6. Re: Calculating Number of Workdays including First Day

If you are wanting May 19th as the result (rather than May 22nd with your version) then this modification to your formula seems to do the job:

Let(

StartDate = StartDate - Case(DayOfWeek(StartDate) = 2; 3; 1);

StartDate + Int ( WorkDays / 5 ) * 7 + Middle ( "12345012340123601256014560345623456" ; ( DayOfWeek ( StartDate ) - 1 ) * 5 + Mod ( WorkDays ; 5 ) + 1 ; 1 )

)

• ###### 7. Re: Calculating Number of Workdays including First Day

Set up a table with one record for each day on which class can be held. You only need a date field in this table to record the day that it represents. For a holiday, you can find the record for that date and delete it.

Relate it by date to your layout's table occurrence:

LayoutTO::startDate <= ClassCalendar::Date

GetNthRecord ( ClassCalendar::Date ; NumberOfDays)

will then give give you the last day of class.

Note:

I've described the simplest possible set up. I actually had a table with one record for every day of the year and set a field value to mark which days were "open". My relationship matched by date and a status field so that it only linked to open dates.

• ###### 8. Re: Calculating Number of Workdays including First Day

thanks to both philmodjunk and Jaymo,

I used both of your answers which gave me the correct number of days and the correct day of the week. Then I added the Holiday table as described and it all works great.

Thanks again.

• ###### 9. Re: Calculating Number of Workdays including First Day

You are most welcome. Happy FileMaking!