If statement using dates - what am I doing wrong?
- 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
Hello,
I'm trying to categorize generations by using a date of birth field and dates, but I'm getting a malformed statement error and cannot figure out why. Any help would be appreciated! Formula is listed below.
Thanks,
Caroline
If [Date of Birth] <= 1945-12-31 then "Matures (1909 - 1945)"
else if [Date of Birth] >= 1946-01-01 and [Date of Birth] <= 1964-12-31 then "Baby Boomers (1946-1964)"
else if [Date of Birth] >= 1965-01-01 and [Date of Birth] <= 1980-12-31 then "Generation X (1965-1980)"
else if [Date of Birth] >= 1981-01-01 and [Date of Birth] <= 1996-12-31 then "Millennials (1981-1996)"
else if [Date of Birth] >= 1997-01-01 then "Generation Z (1997-present)"
else Null()
endif
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
If [Date of Birth] <= "1945-12-31" then "Matures (1909 - 1945)" elseif [Date of Birth] >= "1946-01-01" and [Date of Birth] <= "1964-12-31" then "Baby Boomers (1946-1964)" elseif [Date of Birth] >= "1965-01-01" and [Date of Birth] <= "1980-12-31" then "Generation X (1965-1980)" elseif [Date of Birth] >= "1981-01-01" and [Date of Birth] <= "1996-12-31" then "Millennials (1981-1996)" elseif [Date of Birth] >= "1997-01-01" then "Generation Z (1997-present)" else Null() endif
or
If [Date of Birth] <= "1945-12-31" then "Matures (1909 - 1945)" elseif [Date of Birth] <= "1964-12-31" then "Baby Boomers (1946-1964)" elseif [Date of Birth] <= "1980-12-31" then "Generation X (1965-1980)" elseif [Date of Birth] <= "1996-12-31" then "Millennials (1981-1996)" elseif [Date of Birth] >= "1997-01-01" then "Generation Z (1997-present)" else Null() endif
Dates are treated like strings, so they need quotes.
Also, if i already know that you were born after 1945-12-31 (else you are matures) do you really need to ask again in the elseif? I removed those repeat questions in the second logic set.
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for your question! Anytime you are hard-coding a string or date value in an expression, the value must be in quotes. Only numbers do not need quotes around them. Please see an example below:
[DateTime_Out] >= "2018-01-03"
I would double check that your [Date of Birth] field is in a "date" data type and add quotes around the dates in your conditions. This will ensure that everything works as you would expect.
Have a great day!
Amelia
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
Thanks for the quick response! I made the changes that both of you suggested but am still seeing the malformed if statement error. I did change the "Date of Birth" field to a date using a select tool. Any other thoughts? Thanks for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you for the quick response! Still getting an error. See my note to Amelia below. Thanks for your help!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
elseif
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
That was it! Thanks!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Quick is good, but accurate (right) is even better!
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi, I am having the exact same issue and I don't see that this was ever resolved. Could someone please advise?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
"Else If" has a space in it.
Syntax requires: ELSEIF
Cheers,
Mark
Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
