cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

countig days and deduct holidays from a separate list

Hello, everybody,


currently i am struggling with a problem where a function should count the holidays. Afterwards the holidays should be subtracted from a separate list. at the moment this is done with the following formula:

 

"1 + RoundDown(DateDiff(DateValue1.SelectedDate; DateValue2.SelectedDate; Days) / 7; 0) * 5 +
Mod(5 + Weekday(DateValue2.SelectedDate) - Weekday(DateValue1.SelectedDate); 5) - CountIf(Table1.Datum; Datum >= DateValue1.SelectedDate; Datum <= DateValue2.SelectedDate)"

 

 

But because of the 1 + (at the very beginning, which is unfortunately necessary, it subtracts one day too few from my list, the system should find holidays in the list.

How can I now create an If Abfarge, which checks if the "CountIf" has a value >= 1 and only then (and only then!) adds another one to it?

 

thanks a lot in advance for a tip

 

Sebastian

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

@Anonymous 

Try this:

 

With( 

   { holidays: CountIf(Table1.Datum; Datum >= DateValue1.SelectedDate; Datum <= DateValue2.SelectedDate) }; 

1 +

RoundDown(DateDiff(DateValue1.SelectedDate; DateValue2.SelectedDate; Days) / 7; 0)

* 5 +
Mod(5 + Weekday(DateValue2.SelectedDate) - Weekday(DateValue1.SelectedDate); 5)

- If( holidays>=1, holidays, 0)

)

 

Note: 'holidays' is just a variable name I came up with, you can use a different name that means something to you.

View solution in original post

7 REPLIES 7
Super User
Super User

@Anonymous 

Try this:

 

With( 

   { holidays: CountIf(Table1.Datum; Datum >= DateValue1.SelectedDate; Datum <= DateValue2.SelectedDate) }; 

1 +

RoundDown(DateDiff(DateValue1.SelectedDate; DateValue2.SelectedDate; Days) / 7; 0)

* 5 +
Mod(5 + Weekday(DateValue2.SelectedDate) - Weekday(DateValue1.SelectedDate); 5)

- If( holidays>=1, holidays, 0)

)

 

Note: 'holidays' is just a variable name I came up with, you can use a different name that means something to you.

View solution in original post

Anonymous
Not applicable

Thanks, Eelman,


but I think there's been a little misunderstanding here. (or a misunderstanding on my part)

 

Because basically I only need the option to get an additional "+1" if the CountIf formula is >= 1.

Would this be possible to solve with an If formula? Something like this:

 

If(CountIf(Table1.Date; Date >= DateValue1.SelectedDate; Date <= DateValue2.SelectedDate); +1; +0)

 

so if CountIf is greater than or equal to 1, add 1 and if not, then add 0.

 

 

If I take over your formula like this, the system will only give me errors.
(especially in the last If area, because shouldn't the last holiday be already provided with +1?)

 

 

Thanks a lot already.

Sebastian

Anonymous
Not applicable

It was my fault. ;- )


I just had to swap the commas for semicolons and it worked.

 

Thank you!

 

do you have a tip for another problem kit of the formula?

 

Because if I have entered May 1st as a holiday in my Excel list, for example, and now select this as the end date in the field "DateValue2.SelectedDate", the selection does not work. However, if I select May 2nd as the end date, then the CountIf rule applies and the system counts May 1st as a holiday. I am surprised, because I have explicitly specified <= and >= when comparing the dates.

 

  1. Ah ok,

Seems you've solved it yourself. Just replace your original code snippet with this one and you're done

 

Edit: whoops I must've been replying as you were posting

@Anonymous 

It sounds like it may be an issue with the time portion of your date. You could check if the Excel dates also have time portions added - click the cell and look at the formula bar to see if there is a date and time showing.

 

Could you also explain how DateValue2.SelectedDate is getting created or where it comes from? It's time portion maybe the issue?

 

To check if time portions are the issue, you could do some True/False testing with labels in your app and also the same within your Excel file. In the app you could use the Date function to compare Like this

 

Datum = Date ( Year( DateValue2.SelectedDate ); Month ( DateValue2.SelectedDate); Day( DateValue2.SelectedDate))

 

place this in a new label and see the result

 

Apologies if this is a little unclear so let me know if you get stuck and I'll explain some more

Anonymous
Not applicable

Thank you for your support!


Maybe I have found the problem.
Because apparently, the SharePoint does not import the exact data from my Excel spreadsheet. As you can see on the attached picture, I saved the date information on an English system with English Office converted. But the SharePoint ignores my time data completely. If I want to import only the date without a time, the SharePoint writes any time in the respective rows.

What is wrong with the import, that the SharePoint independently writes clock times or does not take over my times?

@Anonymous 

The Time portion does appear to be the issue.

 

How is the Excel data imported to SharePoint? Could you not just type the dates into your SP column? Is the SP column a Date ONLY or a Date/Time column? My understanding is that if you add a Date to a Date column in SP it sets the time to 12:00am but Default. I'm not sure why there are different times, could it be when these dates were added to SP?

 

 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (56,276)