Skip to main content

Notifications

Community site session details

Community site session details

Session Id : h4wBqSx+ZUigZqD5T9OP6M
Power Apps - Microsoft Dataverse
Unanswered

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

Like (0) ShareShare
ReportReport
Posted on 16 Jun 2023 14:20:32 by 11

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!

  • Parvez Ghumra Profile Picture
    1,579 Super User 2025 Season 1 on 16 Jun 2023 at 17:33:28
    Re: Using a calculated field and DIFFINDAYS() to exclude weekends?

    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=customerserviceadmincenter

     

    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)

  • Ami K Profile Picture
    15,665 Super User 2024 Season 1 on 16 Jun 2023 at 17:24:12
    Re: Using a calculated field and DIFFINDAYS() to exclude weekends?

    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:

     

    Amik_0-1686935947768.png

     

    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.

     

     

     
     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,743 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 66,089 Most Valuable Professional

Leaderboard

Featured topics