Skip to main content
Community site session details

Community site session details

Session Id : yyYTsqt6mezgf/MxSLHBwM
Power Apps - Building Power Apps
Answered

Subtract a numerical value from the value of another formula based on the value of another column

Like (0) ShareShare
ReportReport
Posted on 19 Apr 2021 21:43:04 by 33

Hello all,

 

Im in the process of building a leave request app. I have a column which totals the Days Taken using the datediff between a start date and end date, it also omits weekends and holidays from an embedded spreadsheet using the forrula below;

 

RoundDown(DateDiff(fld_Start_Date.SelectedDate, fld_End_Date.SelectedDate, Days) / 7, 0) * 5 +
Mod(5 + Weekday(fld_End_Date.SelectedDate) - Weekday(fld_Start_Date.SelectedDate), 5)
-CountIf(Table1, HolidayDate >= fld_Start_Date.SelectedDate, HolidayDate <= fld_End_Date.SelectedDate)

 

This works great. If had a request to account for half days, so I created a dropdown in my list called day type, the selections are "Half Day" or "Full Day", if Half Day is selected I want the Days Taken field to only read .5, anything else I want the column to be the result of the formula above. 

 

No matter what I try, the formula seems to error. Can anyone see where I'm going wrong here?

 

dkent_0-1618868745533.png

 

 

 

 

Categories:
  • v-qiaqi@microsoft.com Profile Picture
    on 22 Apr 2021 at 01:23:28
    Re: Subtract a numerical value from the value of another formula based on the value of another column

    Hi @dkent,

    Glad that you have solved your problem.

    To explain more, What I want to know is the formula you set for the Drop down, I do really know that you populate it in Power Apps. 

    I guess you set the Items as DataSource.ColumnName

    Since you have solved your problem, I want to tell you that you have used a Text column to fill in the Drop down, so it should be DD.Selected.ColumnName when you want to refer to DD.Selected.

    Please mark your own reply as answer to help more people to find it.

    Regards,

    Qi

     

  • Verified answer
    dkent Profile Picture
    33 on 21 Apr 2021 at 12:55:12
    Re: Subtract a numerical value from the value of another formula based on the value of another column

    Hello, I figured it out my syntax was wrong, I changed to fld_day_type.Selected.Title and id works fine. But to answer your question, the DD is populated from within a powerapp.

  • v-qiaqi@microsoft.com Profile Picture
    on 20 Apr 2021 at 09:08:57
    Re: Subtract a numerical value from the value of another formula based on the value of another column

    Hi @dkent,

    Based on the issue that you mentioned, do you want to write an If statement based on the Dropdown selected?

    Could you please tell me that how you set the Items property of your day type Drop down?

    The key to your issue is the Items property of the day type Drop down.

    Tell me the Items formula and the error message.

    Regards,

    Qi

     

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

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete