Trying to identify missing dates

Hi guys,

I'm currently facing an issue with a task regarding identifying missing dates I feel should maybe not be this hard.

Essentially I have a calendar below which contains all the dates starting from 2023-01-02 up until today 2023-01-29. I then have a second dataset that contains a list of emails and the dates in which someone completed an MS form. What I'm trying to do is figure out what dates are missing from the Ms Forms data set based on the Calendar data and the generate the missing rows

This sounds like something that should be easy but I'm just drawing a big blank.

Cheers for any input and or help in reaching a solution.

Dates Data.xlsx

Email and Completion Dates.xlsx

Workflow

Date Time

Help

Join

Accepted answers

alexnajm

If you are just comparing the two datasets and their dates, a Join tool should work well! Otherwise I'd use a Multi Row Formula to fill those nulls - see attached for both.

Trying to identify missing dates.yxzp

All comments

aatalai

@Deano478 have you tried using the ts filler tool under time series that might be your best bet. will give you a list of all dates between two dates and whether or not the are in the dataset. Let me know if that helps?

alexnajm

If you are just comparing the two datasets and their dates, a Join tool should work well! Otherwise I'd use a Multi Row Formula to fill those nulls - see attached for both.

Trying to identify missing dates.yxzp

Deano478

Hey @alexnajm cheers that worked my follow on question is also that I need to just generate the date of year number for each person for each date so like an id with jan 1 being 1 and December 31 being 365. I hope tats no too much trouble my apologies.

alexnajm

@Deano478 you can use %j in a DateTime function then to get day of the year! DateTime Functions (alteryx.com)

DateTimeFormat([Date],"%j")

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703