Working days calc

[troyhoskison]

Hi,

I have a CDS table that has a field in that is the Customer due date.

From this I need to work out the internal due date, which is 2 days before the customer due date. However i need 2 working days not two actual days.

• A calculated field it limited to only actual days, not working days.
• Business rules have the same limitations
• Power automate might work, but it only runs once every 5 minutes, so not useful in a production environment

Any thoughts on how I sort this problem out?

The second part is; once I have the internal due date I need the start date, which is the internal due date minus the lead time.

Thanks

Troy

[dpoggemann]

I just tried it and it came up for me…

[Anonymous]

@dpoggemann and we're talking about the github link referring to Jason's workflow utility, right?

[troyhoskison]

looks like it has moved

GitHub - steveplatz/CRM-DateTime-Workflow-Utilities: Custom workflow actions that deal with date and...

[Anonymous]

Thanks @troyhoskison and @dpoggemann 

Would this utility by steve calculate the number of business days between dates?

[Anonymous]

@dpoggemann @troyhoskison 

Would this work for D365? The versions outlined go up until 2016 for this utility.

[troyhoskison]

I am using it in the current dataverse. the only issue I found is that if you want to put in holidays, it doesn't work unless you have CRM installed, as the table/calendar it references is Dynamics CRM only. 

[Anonymous]

@troyhoskison 

Oh! That makes it a bit counter productive for me then because we don't have the full CRM license installed. We do want to put in holidays. I was thinking if I created a "Business Closure" table, would this work somehow?

Are you calculating using holidays? If so, how?

[troyhoskison]

unfortunately, I am not. I raised a call with MS about it and it confused them for some time. They said that you wouldn't need a license for CRM but just have it installed. Obviously, I didn't want to install something that I am not using. I think I will be doing this a different way when I get around to re-doing it. We are looking at moving to Business central and I was hoping that would include the business closure table too

[Anonymous]

That's discouraging. I guess I'll need to find another way with the given tables. Obviously we don't have a Business Closures table so will have to see how I can work around that. I believe it can be done via a Flow but then I'm not well versed..yet.

And maybe you might find this helpful if you want to look at tables and licensing: License requirements for tables - Power Apps | Microsoft Docs

[troyhoskison]

So I had a look at doing it in PowerAutomate, it is possible, but it is a long-winded process. You have to move the days and check for ‘day of the week’ and it is in the closure array for each day. there are a few guide to this on the internet like this one...

Calculating working days in Power Automate or Logic Apps (chorus.co)