Identify if today's date is between 2 dates

[Tob_P]

Hi.

I have created a column in Query Editor for today's date...

= Table.AddColumn(#"Added Custom", "Today's Date", each DateTime.LocalNow() as datetime)

From that I would like to identify if Today's date is between 2 dates 'Agreement Start Date' and 'Agreement End Date'. I've tried two different solutions both with errors. All Date columns are set to Date.

Failed Attempt 1...

= Table.AddColumn(#"Changed Agreement Start/End dates from date/time to date", "Custom 2", each if Date.From([#"Today's Date"]) >= Date.From([Agreement Start Date]) and Date.From[#"Today's Date"] <= Date.From([Agreement End Date]) then "Active" else "Inactive")

Error = Expression.Error: We cannot apply field access to the type Function.
Details:
Value=[Function]
Key=Today's Date

Failed Attempt 2..

= Table.AddColumn(Custom1, "Custom3", each if Date.Day([Agreement Start Date]) <= [#"Today's Date"] and Date.Day([Agreement End Date]) >= [#"Today's Date"] then "Active" else "Inactive")

Error  = Expression.Error: We cannot apply operator < to types Date and Number.
Details:
Operator=<
Left=20/03/2024
Right=1

Could anyone recommend a potential solution please?

Thanks

[Tob_P]

Thanks!

[lbendlin]

If you want to overcomplicate things you can also use List.Intersect

[slorin · Accepted solution]

Hi

Attempt 1...

Date.From([#"Today's Date"])

= Table.AddColumn(#"Changed Agreement Start/End dates from date/time to date", "Custom 2",
 each if Date.From([#"Today's Date"]) >= Date.From([Agreement Start Date]) and 
    Date.From([#"Today's Date"]) <= Date.From([Agreement End Date]) then "Active" else "Inactive")

Stéphane