Request help to find missing dates in a date range for the user

[anish.ms]

Hi,

Can somebody please help me to find the missing dates in a date range for the current user based on the Application.Username property. In the attached sample workbook, I have dates in column "C" and user names in column "B". I need to find and send a message box to the user for his/her missing dates from the first date available for that user in the table till today.

Thanks in advance

[SamT]

Define missing date. It's not apparent on your sample.

[anish.ms]

Hi Sam,
Thanks for your reply!
Below are the user wise missing dates from the sample attached previously




Min Date*
Current Date
Missing Dates


User1
15-Apr-2021
22-Apr-2021
16/Apr/2021, 18/Apr/2021 and 19/Apr/2021


User2
16-Apr-2021
22-Apr-2021
17/Apr/2021, 19/Apr/2021 and 20/Apr/2021



*Min date is the first date available for that user in the excel table. Same way if there are no entries for a user, then the message should be "no data available"

[SamT]

Review Dates should include every day, including weekends???

As a Programmer. IMO, you should treat all data like a Database, rather than like a daily log. If your data is Database style, your Reports can still be in Log style for human consumption. Database style is incredibly much easier to Code and Formulate for.

[p45cal]

In the attached there's a table at cell K1.
Update your table on the left, then right-click on the table on the right and choose Refresh.
I've used the max date for each user rather than today's date - easily tweaked if necessary.

[anish.ms]

Hi Sam,
Thanks for your response!
Yes, holidays and leaves are also captured. Hence, the number of missing dates should be very less/nil in real data.

[anish.ms]

Thanks p45cal!
I would prefer to have a VBA code than power query because I need to find out the missing dates and send a message to the user based on a button click on the user form.

I'm getting the below compatibility error when I go to PQ editor, however the missing dates are updated on refresh.

28352

[p45cal]

Then we need to know what the weekend days are (you'll know that they differ internationally) and a list of holiday dates somewhere.

[anish.ms]

Thanks p45cal!
Fridays and Saturdays are the weekly off days.
Weekly offs, holidays and leaves are also updated by the users in the tracker. Hence no need to exclude any dates from the test.

[p45cal]

In the attached, button at cell N3 and one at N11 each calling different versions of a macro - self explanatory.
I note that both users have review dates that fall on a weekend?!
Weekends are excluded from the list of dates, holidays would be included since I don't know when the holidays are.
A line in the macro should be deleted for real use:
UsrNme = "User2" 'temp line,delete for real use.

[anish.ms]

Thanks a Ton p45cal!
I will check and revert in case of any doubts.
As an FYI, there are 20 users who will be updating this tracker from2 different locations with different weekly off days. Due to this we have asked the users to update both weekly offs and holidays.
Otherwise I need to have a seperate table for user wise weekly off days to exclude when the code runs
Weekly off days are for GCC users Fridays and Saturdays and for India Sunday

[p45cal]

There is a line:
If Application.WorkDay_Intl(dte - 1, 1, 7) = dte Then in the macros which uses the same arguments as the worksheet formula:
=WORKDAY.INTL(Table_DB[@[Review Date]]-1,1,7)=Table_DB[@[Review Date]]
where the 7 determines the weekend days:
You will have to amend that 7 for the regions involved, or you could use a variable in place of the 7 in the code using Application.International(xlCountrySetting) or Application.International(xlCountryCode)
xlCountrySetting is Current country/region setting in the Windows Control Panel
xlCountryCode is Country/Region version of Microsoft Excel
on the lines of:
If Application.International(xlCountrySetting) = 91 then xx=11 else xx=7
then later substitute xx for 7:

If Application.WorkDay_Intl(dte - 1, 1, xx) = dte Then

See:
https://docs.microsoft.com/en-us/office/vba/api/excel.xlapplicationinternational
https://flylib.com/books/en/3.428.1.247/1/
https://countrycode.org/

For xlCountry Setting:
India is 91 and the GCC:
Kuwait 965
Saudi Arabia 966
Oman 968
United Arab Emirates 971
Bahrain 973
Qatar 974
So you could check for am xlCountrySetting > 964 then xx would = 7 otherwise 11

[anish.ms]

Very informative, thanks a lot for your help