countig days and deduct holidays from a separate list

[Anonymous]

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

[Anonymous · Accepted solution]

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

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]

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]

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]

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

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]

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