Calculate Business Days

[leenu_n]

Hi,

I would like to calculate number of business days from a particular given date. This calculation should take into consideration Weekends and some holidays. Is there some way i can do it ? This is to create a schedule for some jobs.

Thanks

L

[LiveOak]

The "some holidays" is the fly in the ointment. Without this, I would use a calculation. In this case I would probably create a file called "business dates" and put one record in it for each business day. Auto enter a serial number for each business day. If you make the business date and serial number lookups into your main file, you can use "if no exact match, use next higher value" to lookup the next valid business date for a calendar date and lookup the serial numbers to find the number of business days between to dates.

-bd

[LeCates]

Hi L,

This formula finds the number of workdays {M,T,W,R,F} between two dates..

Given:

dStart: A date

dEnd: A date

This formula will work regardless of whether the dates fall on Saturday and Sunday, and is "logically simple" with no If() or Case() statements required.

This formula does not, obviously, take holidays into account, but is a reasonable starting point for a system that does. As LiveOak said, holidays will be "the fly in the ointment".

code:

---

= Int((dEnd - dStart)/7)*5 +

Middle("0000000123455012344401233340122234011123400012340012345",

(DayofWeek(dStart)*7) + Mod(dEnd - dStart, 7), 1)

---

Alternative, inclusive of last day if work days are on both ends:

code:

---

= Int((dEnd - dStart)/7)*5 +

Middle("0000000123455123455512344451233345122234511123450012345",

(DayofWeek(dStart)*7) + Mod(dEnd - dStart, 7), 1)

---

[jrp]

I don't know if it's too late for this to be helpful - but I've been working out a similar issue. I have an OK solution, lacking holiday resolution - but I've got an idea for that, and am hoping someone here can help us both figure it out.

I have a sequential list of tasks, each with a given duration (of business days), each with a start date equal to the former end date (I calculate an estimated End Date, then the user has a field they can enter the Actual End Date, and the next process takes this into account). Using the same basic calculation droid gives - with my own additions - I created the list in this way:

Step 1 Start Date

Step 1 Duration

Step 1 Start Date Work (a calculation to be sure we start on a business day - only happens once)

If(IsEmpty(Step 1 Start Date), TextToDate(""),

Step 1 Start Date + Case(DayofWeek(Step 1 Start Date) = 1, 1,

DayofWeek(Step 1 Start Date) = 7, 2))

Step 1 Estimated Complete Date

If(IsEmpty(Step 1 Start Date Work), TextToDate(""),

Step 1 Start Date Work + Int(Step 1 Duration/5) * 7 +

Middle("12345012340123601256014560345623456",

(DayofWeek(Step 1 Start Date Work) - 1) * 5 +

Mod(Step 1 Duration,5) + 1, 1))

Step 1 Actual Date

Step 2 Duration

Step 2 Start Date

If(IsEmpty(Step 2 Duration), TextToDate(""),

If(IsEmpty(Step 1 Actual Complete Date),

Step 1 Estimated Complete Date, Step 1 Actual Complete Date))

Step 2 Estimated Complete Date

If(IsEmpty(Step 2 Start Date), TextToDate(""),

Step 2 Start Date + Int(Step 2 Duration/5) * 7 +

Middle("12345012340123601256014560345623456",

(DayofWeek(Step 2 Start Date) - 1) * 5 +

Mod(Step 2 Duration,5) + 1, 1))

Step 2 Actual Complete Date

...and so on.

Now then - on to the holiday issue. I have a global field that contains all the year's holidays ("gHolidays") - one that can be edited by the user year to year. I want to write a script that looks at the range between "Step 1 Start Date" and "Step 1 Estimated Complete Date" and compares it with the dates in "gHolidays" - if it contains one of those dates, it should add one day to "Step 1 Duration."

This feels conceptually possible - I just can't figure out the calculation. Can anyone help? Or maybe just tell me it's impossible and I should stop trying...

Many Thanks - and I hope this is helpful to leenu_n.

-jrp

[ December 31, 2001: Message edited by: jrp ]

[ December 31, 2001: Message edited by: jrp ]