Adding work days to a date

[kiwiora]

Hi guys,

I'm trying to a number of days to a start date. The days have to be work days.

I have the below calc, but unfortunately it dies at 15 days because at that point you need to start adding in the additional weekends that may fall inside of the date range.

Date04Stgw = work days only

Date04sW = start date (adds date if falls on weekend)

Date04Stg = number of days

TIA

If(IsEmpty(Date04Stgw); Date04sW+Date04Stg ;



Let ($Day = Date04sW + Date04Stg + ((Truncate (Date04Stg / 7; 0)) * 2) + ((Round ( DayOfWeek ( Date04sW ) / 7; 0 )) * 2) ; 



Case ( 

DayName($Day) = "Monday"; $Day;

DayName($Day) = "Tuesday"; $Day;

DayName($Day) = "Wednesday"; $Day;

DayName($Day) = "Thursday"; $Day;

DayName($Day) = "Friday"; $Day;

DayName($Day) = "Saturday"; ($Day + 2);

DayName($Day) = "Sunday"; ($Day + 2);

 )



)



))

[LaRetta]

I'm afraid I can't follow what you are doing; I have no idea what your fields represent. But you have Advanced so why not use a Custom Function? I put this together. Now I KNOW it can be done without the global variable but I don't understand tail recursion and stuff and I did NOT want two Custom Functions - that would give me the willies.

Name: ExtendWkDaz

Parameters:

theDate

numDaz

If ( theDate ;

Let ( [

start = theDate + Position ( "17" ; DayOfWeek ( theDate ) ; 1 ; 1 ) ;

$less1 = numDaz - 1 ;

end = start + 1

] ;

If ( $less1 < 1 ; end ; ExtendWkDaz ( end ; $less1 )

)

)

)

If you search around, you can find better ones, I'm sure. But I wanted to get you going because I think you'll need recursion to jump forward each day (and test whether a weekend) or you'll have to hard-code til the cows come home. If you knew the end date, it'd also be a piece of cake. But, by using a Custom Function, it does the heavy lifting for you and that's what I want to show.

UPDATE: And no, I'm not popping this onto Brian's website; it isn't good enough. And if anyone asks if it's me that did this, I will deny it.

LaRetta

[comment]

It does seem a bit convoluted. If you are going day-by-day, there's no need to push the date forward when it falls on a weekend. Just check if it is a working day. If true, subtract one from the working days left, and move on to the next date - until you run out of working days left:

DatePlusWorkDays ( startDate ; workDays )

Let ( [

workDay = Mod ( startDate - 1 ; 7 ) < 5

] ;

Case (

workDays ; DatePlusWorkDays ( startDate + 1 ; workDays - workDay ) ;

startDate

)

)

But a recursive calculation is not really required here - search fo "anomality" with -Queue- as the author (speaking of cows, I wonder when this particular one is coming home...).

[jrr316]

I was working with this CF and it works Except for a few anomolies:

If the Start Date is Wednesday and I was to add 3 work days. It ends up on Saturday.

If the Start Date is Monday and I was to Add 5 days I end up on Saturday.

Any Ideas on how to fix this?

Edited January 3, 2008 by Guest

[comment]

The way the above version works, it returns the date AFTER n workdays have elapsed (including startDate, if it's a workday). So that:

• Friday + 0 workdays = Friday

• Friday + 1 workdays = Saturday (the day after Friday)

• Friday + 2 workdays = Tuesday (the day after Friday and Monday)

• Saturday + 0 workdays = Saturday

• Saturday + 1 workdays = Tuesday (the day after Monday)

• Sunday + 0 workdays = Sunday

• Sunday + 1 workdays = Tuesday (the day after Monday)

If you want it to always return the NEXT work day, you can try the following modification, with the effect of pushing startDate to a workday - even if 0 workDays are specified:

• Friday + 0 workdays = Friday

• Friday + 1 workdays = Monday (the next workday after Friday)

• Friday + 2 workdays = Tuesday (the next workday after Friday and Monday)

• Saturday + 0 workdays = Monday (the next workday after Saturday)

• Saturday + 1 workdays = Tuesday (the next workday after Monday)

• Sunday + 0 workdays = Monday (the next workday after Sunday)

• Sunday + 1 workdays = Tuesday (the next workday after Monday)

NextWorkday ( startDate ; workDays )

Let ( [

workDay = Mod ( startDate - 1 ; 7 ) < 5

] ;

Case (

workDays or not workDay ; NextWorkday ( startDate + 1 ; workDays - workDay ) ;

startDate

)

)