Using a calculated field and DIFFINDAYS() to exclude weekends?

[sammvarley]

Hi,

Pretty new to Power Apps but I have a table that has two date columns, 'datereceived' and 'datecomplete'.

Following this is a number column, 'count of days' which just needs to calculate the days between datereceived and complete. However, it needs to exclude weekends from the count. I've Googled a lot and people seem to have different functions to me, so I can't find anything useful (DateDiff).

Can anyone help please?

As a bonus, I'd also like to add a 'deadline date' column which is calculated from 'datereceived' + a number column 'Days required'. This would also need to exclude weekend and populate 'Deadline Date'.

Thanks!

[Amik]

Hi @sammvarley .

Calculated columns with Date columns are currently problematic in Dataverse.

We could in theory use something like below to obtain the date difference between two dates excluding weekends:

RoundDown(DateDiff('Date Column End','Date Column Start', TimeUnit.Days) / 7, 0) * 5 +
Mod(5 + Weekday('Date Column Start') - Weekday('Date Column End'), 5)

Although the functions applied above are supported (RoundDown, Mod, DateDiff, Days, Weekday), attempting to apply this to a PowerFx formula will return the following error:

This is due to the lack of support at present for the localisation of Dates in Dataverse.  Time zone conversion is not yet supported in calculated Dataverse columns.

You can find detailed information about this here:

http://powerappsguide.com/blog/post/dataverse-formula-columns

As an alternative, I would suggest using Power Automate to do achieve this.

------------------------------------------------------------------------------------------------------------------------------

If I have answered your question, please mark your post as Solved. Remember, you can accept more than one post as a solution.

If you like my response, please give it a Thumbs Up.

[parvezghumra]

Hi @sammvarley 

Are you using Dynamics 365 for Customer Service. If so, I'd recommend looking into SLAs. They're quite feature rich and allow you to build some powerful which just some simple configuration: https://learn.microsoft.com/en-us/dynamics365/customer-service/define-service-level-agreements?tabs=...

If this is not an option,  you might want to try to build what you need using the Power Fx formula columns. You can read more about these here: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns

If this also doesn't work, you'll probably need to write some custom code (eg. plugins) to build your desired functionality as I don't think it will be achievable using basic configuration (eg. calculated columns etc)