Formula to determine if a calendar entry overlaps with date

@Aleksi I see you have previously given an answer to a similar question:


Your answer was in the context of checking to see if it exists so to speak:

COUNT(SELECT(Table[KeyCol],
AND(
[_THISROW].[Date]>=[StartDate],
[_THISROW].[Date]<=[EndDate])
))=0

I am looking to determine what events overlap with a date and list them as enum options.
an example being
task 1 from 1/1/2020 to 3/1/2020
task 2 from 3/1/2020 to 5/1/2020
task 3 from 1/1/2020 to 3/1/2020

todays date is the 2/1/2020
I would now like an enum column to detect the overlaping events and list task 1 and task 3 as options.

I am also unsure of where to place the expression to create this column.

Thank you

Is the value exatly like “task 1 from 1/1/2020 to 3/1/2020”? Do you read these values from another table?

@Aleksi
There is a schedule table used to add tasks to the calendar.

there is a timesheet table used to record users work time, i want to allocate tasks to work times.

the values change depending on the schedules entry

the ‘task 1’ column is simply a job description so it will change regularly
the dates referenced are start date and end date columns used in the calendar view

So… in generally you have three columns… “Job description”, “Start date” and “End date”, correct? If that’s correct, you could read descriptions like SELECT(TableName[Description],AND([Start Date]<=TODAY(),[End Date]>=TODAY()))

1 Like