Get number of holiday based upon array input based on LinQ

Help Studio
, , , , , ,
1

I am getting input as {“23/01”,“23/03”, " 23/04"}

image
image588×680 10.8 KB

Based upon this input I have to find the count of holidays in the below Calendar

image

Like in this scenario, Once I will iterate through Array {“23/01”,“23/03”, " 23/04"}
for
23/01 - If we look at the PP calendar ( 1st screenshot) we will get the from date 12/24/22 i.e. 24th Dec 2022 and to date 01/08/23 i.e. 8th Jan 2023. now we have to search for this date in the holiday calendar. I should get count 2 ( First holiday on 25th Dec 2022 and second holiday on 1st Jan 2023.
Similarly for 23/03 - I should get a count of 0 ( start date 23rd Jan 2023 to 05th Feb 2023)
Similarly for 23/04 - I should get count 1 ( There is only one holiday on 13th Feb 2023 ( start date 06 Feb 2023 to 19th Feb 2023)

All the dates in Excel are in MM/dd/yyyy.

I have attached both Excel also. Any experts please help.
Holiday Calendar.xlsx (28.3 KB)
PP Calender.xlsx (10.8 KB)

2

@c3f1e68294fdcf4a1f0a817ca

On what basic is 23/01 has 2 holidays?

Cheers

3

Hi @Anil_G Thanks for your reply.
My bad, I have corrected it.

23/01 - If we look at the PP calendar ( 1st screenshot) we will get the from date 12/24/22 i.e. 24th Dec 2022 and to date 01/08/23 i.e. 8th Jan 2023. now we have to search for this date in the holiday calendar. I should get count 2 (one holiday on 25th Dec 2022 and another holiday on 1st Jan 2023.

4

@c3f1e68294fdcf4a1f0a817ca

Try like this

  1. Say the pp table is read into dt1 and holidays into dt2
  2. Use a for loop on thr array with the input pp values
  3. Inside loop use assign with requiredholidayarray = dt2.AsEnumerable.Where(function(x) CDate(x(0).ToString) > dt1.AsEnumerable.Where(function(y) y(0).ToString.Equals(currentItem)).Select(function(y) Cdate(y(1).ToString)).First AndAlso CDate(x("Holiday 2023").ToString) < dt1.AsEnumerable.Where(function(y) y(0).ToString.Equals(currentItem)).Select(function(y) Cdate(y(3).ToString)).First).Select(function(x) x(0).ToString).ToArray

Cheers

5

Thanks @Anil_G but Getting this error message
BC30690: Structure ‘Char’ cannot be indexed because it has no default property.

6

@c3f1e68294fdcf4a1f0a817ca

can you please check it again…as I see no errors when used

also in the for loop I believe you are looping array and not string type variable

It would be helpful if you can show what you used exactly and the datatypes of each of the variables

cheers

1 Like
8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.