Calculate Due Date Based On Work Days

I found the netWorkDays function, however I don't think it applies to what I want to accomplish.

I want to track jobs with a due date of 7 business days after initiation.  So if a job (record) is begun (created) on 9/1, I want the system to automatically calculate the due date that comes 7 business days after 9/1.

Does anyone know how to accomplish this?

Hello Givin ,

 

Yes i forgot you you need to add 7 working days ..

It's always cross 1 Saturday and 1 Sunday . So Instead you can add 9 directly and get the solution (No need any tough calculation . But if you need less then 7 days then we need some calculation) 

Thanks 

Hi Gavin - 

Here's your solution: 

Start by making your 5 fields as follows: 

Original Date field: Nothing special. This is the 'driver' or 'input' field.

OriginalDayOfWeek field: 

 

Select WkDaySerial field: ;

...Settings: Set Options 1,2,3,4,5

...Conditional Rules: 

(etc...)

Algorithm for 7th Workday field: 

Copy and paste this equation: 

{Select WkDaySerial}==1? 9 : ({Select WkDaySerial})==2? 9: ({Select WkDaySerial})==3? 9: ({Select WkDaySerial})==4? 11: ({Select WkDaySerial})==5? 11: 0

 

Output Date (Due Date) field: 

 

this is the result for 9/18/2019: 

 

if you have more questions let me know!

Jonathan 

avdataco@gmail.com

432-978-4985

 

 

@Sunny - that formula counts all days, not just workdays.

@Julian - I suspect you may be correct, a set of nested "ifs" might be the most efficient way to go with this.

Hi Gavin

You're correct - netWorkDays is basically the inverse of what you need - it works out the number of work days between two dates.

I think what you need to achieve would be possible using some nested ifs - based on which day of the week the staring date is you can work out how many days to add for it to be 7 working days.

Julian

Hello Gavin,

you can use formula field. 

 

Regards,

Sunny Singla

ssingla1985@gmail.com

+919855089359

I can’t seem to see the images from this post anymore.
So I’ll post my solution here, for anyone else who needs it in future.
Thanks to @Callum.Boase for this solution.

The goal: add any number of work days to a start date.

We need 4 fields:

  • Start date: a standard date field
  • Work days to add: a number field containing the number of work days to add to Start date. This can be set to any integer
  • Days to add equation: an equation field: Equation type: “Numeric”. Equation value:
{Work days to add} + (ceil(({Work days to add}+(getDateDayOfWeek({Start date})-(ceil(getDateDayOfWeek({Start date})/6)-1)*6)-1)/5)-1)*2 + ceil(getDateDayOfWeek({Start date})/6-1)
  • Calculated date: another equation field. Equation type: “Date”, Date type: “Days”, result type: “Date”. Equation value:
{Start date}+{Days to add equation}

Known limitations:

  • This assumes that every weekday is a work day.
  • Only works for adding, does not work for subtracting work days
1 Like