Jump to content

Adding work days to a date


This topic is 5384 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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);

 )



)



))

Link to comment
Share on other sites

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. :jester:

LaRetta

Link to comment
Share on other sites

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...).

Link to comment
Share on other sites

  • 1 year later...

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 by Guest
Link to comment
Share on other sites

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

)

)

Link to comment
Share on other sites

This topic is 5384 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.