How to check between dates and include Nulls
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
I have this formula:
IF
([Date1] > '2022-11-01') AND ([Date2] < '2023-12-31')
THEN Null()
ELSE 'Columns has dates outside November'
ENDIF
but if Date 1 or Date 2 column have Nulls in them, how do I take in account for them? Wont I get Nulls if I dont have a date?
Solved! Go to Solution.
- Labels:
- Custom Tools
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @wonka1234
You can account for the null in a column using the isNull() expression
You can also replace these nulls with a blank value referenced by ""
Hope this helps. Please let us know if you have any other questions.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
having trouiule wrapping my formula to account for nulls.
New column:
IF
([Date1] > '2022-11-01') AND ([Date2] < '2023-12-31')
THEN Null()
ELSE 'Columns has dates outside November'
ENDIF
But what if Date1 or Date2 columns have a null int hem? I need a way to handle those nulls and count those rows as a Null.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I believe you are looking for a formula that is shown in the image below.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
thank you, what if I wanted to change your Row 4 "Date Final" to be Null as one of the values there is null?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Please see the second formula in the screenshot below.
Please like this post if this helps and if it helps resolve your problem, mark it as a solution. If you have any other questions, please let us know.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @wonka1234
You can use the following formula to achieve the same result. Please see below.
if ([Date] >= "2022-11-01" and [Date] <= "2023-12-31") then "" elseif IsNull([Date]) then "" elseif IsNull([Date2]) then "" else "Outside November" endif
