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
Anonymous
Not applicable

@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
Anonymous
Not applicable

@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.

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.

 

Anonymous
Not applicable

  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
Not applicable

@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
Not applicable

@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
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,825)