How to get missing dates from excel sheet except saturdays and sundays of respective months

I have an excel sheet in which I have dates (dd.MM.yyyy format) of all months of 2022 under date column. They could be mixed up also.Like after a date of august month, i could have a date of october like that. saturdays and sundays are not written(missing) under this column for all months. But some dates except satday and sunday are also missing. I need to find out the missing dates except satdays and sundays! What should be my approach. please help out

Hi @Siddharth_Kumar1 ,

We could maybe perform this by first Ordering the Dates retrieved from Excel, And Find out the First Date and Last Date in Order, calculate the Total Days between the two Dates. Then Iterate through these range of days and Checking if it exists in the List already or is a Weekday.

Steps as below :

  1. Read the Excel sheet as a Datatable, say DT.
    image

  2. Intialise an Array with the week day values. Retrieve only the Dates from the Datatable, Order/Sort it and Store it in an Array.

weekDays = {"Saturday","Sunday"}
datesArray = DT.AsEnumerable.Select(Function(x)CDate(x("Date").ToString)).OrderBy(Function(x)x).ToArray

Here, weekDays is a variable of type Array of String and datesArray is a variable of type Array of DateTime.

  1. Perform the Check through the Range of Dates and filter out the Missing Dates.
    image
missingDates = Enumerable.Range(1,CInt((datesArray.Last.Date-datesArray.First.Date).TotalDays)).Where(Function(x)Not(datesArray.Contains(datesArray.First.Date.AddDays(x)) orElse weekDays.Contains(datesArray.First.Date.AddDays(x).DayOfWeek.ToString))).Select(Function(x)datesArray.First.Date.AddDays(x)).ToArray

Here, missingDates is a variable of type Array of DateTime.

Debug Panel :
image

If Exceptions occur on Date Conversion, we first would ask you to check the format of Dates in the Debug Panel, so that we can parse the date to appropriate format.

Let us know if you’re not able to follow the Steps.

1 Like

Thanks a lot @supermanPunch . I will try and let you know :blush:. is there any other way without linq? and if i could have data from any month and any date nonsequentially, do i need to order them first manually monthwise and then date wise in excel?