Forum Discussion

APD_Karl's avatar
APD_Karl
Regular Visitor
6 years ago

Getting data from a particular column in a sharepoint list based on two conditions in another list

I am trying to remove weekends and public holidays from a date range. I have the weekend removal working but the formula for holidays is proving beyond my understanding. I have a "counif" formula at the end that would work if I just had one set of public holidays, but unfortunately my organisation crosses multiple regions with different holidays. (See my formula so far at the end of this post).

 

I have 2 SharePoint Lists - (1) APD Staff List and (2) APD Public Holidays. The first list has all our Staff Names and their Location (NSW, NT, QLD, SA, VIC, WA, NZ) and I need to get the dates from the corresponding "Location" Column in the second list. (See Images Below).

 

I'm not even sure which function I need to use - "If/Else", "Calculate" or "column=condition1 && condition2". Please help, I'm losing sleepp over this now.

 

APD Staff List

APD Public Holidays

 

RoundDown(DateDiff('Leave Start Date_DatePicker'.SelectedDate, 'Leave End Date_DatePicker'.SelectedDate, Days) / 7, 0) * 5 +
If(Weekday('Leave End Date_DatePicker'.SelectedDate) >= Weekday('Leave Start Date_DatePicker'.SelectedDate),
If(Weekday('Leave End Date_DatePicker'.SelectedDate) = Weekday('Leave Start Date_DatePicker'.SelectedDate) && (Weekday('Leave End Date_DatePicker'.SelectedDate) = 1 || Weekday('Leave End Date_DatePicker'.SelectedDate) = 7),
0,
If(RoundDown((Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate))/5,0) >= 1,
5,
If(Weekday('Leave End Date_DatePicker'.SelectedDate) = 7,
Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate),
If(Weekday('Leave Start Date_DatePicker'.SelectedDate) = 1,
Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate),
Mod(Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate), 5) + 1)))),
(7 - Weekday('Leave Start Date_DatePicker'.SelectedDate) + Weekday('Leave End Date_DatePicker'.SelectedDate) - 1)) 
- CountIf('APD Public Holidays', Date >= 'Leave Start Date_DatePicker'.SelectedDate, Date <= 'Leave End Date_DatePicker'.SelectedDate)

 

 

Need Help

5 Replies

    • APD_Karl's avatar
      APD_Karl
      Regular Visitor
      6 years ago

      Thanks Greg_Deckler, but that unfortunately doesn't solve my problem. It shows how to remove weekends (Which I've done) and remove a single date dd/mm/yyyy. It doesn't show how to detime which colomn of dates to reference in a second list based on criteria from a first list. That's the part that is confusing me the most.

       

      I am building a Leave Application Form in Power Apps.

      1. The User selects their [Staff Name] from a Sharepoint List "APD Staff List".
      2. This same list has a column [Location] which tells me which region that staff member works in.
      3. A second SharePoint List "APD Public Holidays" has dates in columns with headings based on the location value in the first List.

      So from list "A" - I know (1), I need to determine (2) and use that value to get the dates out of list "B" where the column heading = value (3).

       

      Then I need to combine that with the CountIf statement.

       

      This will show how little I know but Something like... 

      - CountIf (( If 'APD Staff List', [Staff Name] Then 'APD Staff List', [Location] = "value" && 'APD Public Holidays', [value] = "Date") Date >= 'Leave Start Date_DatePicker'.SelectedDate, Date <= 'Leave End Date_DatePicker'.SelectedDate)

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        I have the removal of Holidays in there. Unless I am mistaken, you want EXCEPT.

         

        Not sure if I understand your issue fully honestly. Sample data (text), expected output would be very helpful.