Date Range Formula with multiple

Platform discussions Special workflows & use cases
1

Hi All and @Jim - The Monday Man,

Can anyone help with a formula ?

Create monday.com formula, if IF date in {Service period from} to {Service period to} falls in between dates in parentheses then display text

Korean War (6/27/1950-1/31/1955)
Merchant Marine (12/7/1941-8/15/1945)
Persian Gulf War (On or after 8/2/1990)
Post-9/11 (On or after 9/11/2001)
Post-Korean (2/1/1955-2/27/1961)
Post-Vietnam (5/8/1975-8/1/1990)
Pre-Korean (1/1/1947-6/27/1950)
Vietnam Era (2/28/1961-5/7/1975)
World War I (4/6/1917-11/11/1918)
World War II (12/7/1941-12/31/1946)
If {Service period from} is empty
**Display “**Unknown”

IF({Service period from} >= DATE(2001, 9, 11), “Post-9/11 (On or after 9/11/2001)”,

IF({Service period from} >= DATE(1990, 8, 2), “Persian Gulf War (On or after 8/2/1990)”,

IF({Service period from} >= DATE(1980, 1, 1), “War on Drugs”,

IF({Service period from} >= DATE(1975, 5, 8), “Post-Vietnam (5/8/1975-8/1/1990)”,

IF({Service period from} >= DATE(1961,2,28), “Vietnam Era (2/28/1961-5/7/1975)”,

IF({Service period from} >= DATE(1955, 2, 1), “Post-Korean (2/1/1955-2/27/1961)”,

IF({Service period from} >= DATE(1950, 6, 27), “Korean War (6/27/1950-1/31/1955)”,

IF({Service period from} >= DATE(1947,1,1), “Pre-Korean (1/1/1947-6/27/1950)”,

IF({Service period from} >= DATE(1941,12,7), “World War II (12/7/1941-12/31/1946)”,

IF({Service period from} >= DATE(1941, 12, 7), “Merchant Marine (12/7/1941-8/15/1945)”,

IF({Service period from} >= DATE(1917,4,6), “World War I (4/6/1917-11/11/1918)”,

“Unknown”)))))))))))

Screenshot 2023-11-02 at 8.00.10 PM
Screenshot 2023-11-02 at 8.00.10 PM768×188 15 KB

2

Here is your formula working in a basic sense. Although it does not account for the to date. What logic exactly do you want there? What if you have overlapping ranges? (e.g. the from date is during the war on drugs and the to date is during the Gulf War)

There may be other ways to clean this up, but I’ve found using the FORMAT_DATE function is often the most reliable way to make date comparisons.

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("2001-9-11","YYYYMMDD")), "Post-9/11 (On or after 9/11/2001)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1990-8-2","YYYYMMDD")), "Persian Gulf War (On or after 8/2/1990)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1980-1-1","YYYYMMDD")), "War on Drugs",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1975-5-8","YYYYMMDD")), "Post-Vietnam (5/8/1975-8/1/1990)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1961-2-28","YYYYMMDD")), "Vietnam Era (2/28/1961-5/7/1975)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1955-2-1","YYYYMMDD")), "Post-Korean (2/1/1955-2/27/1961)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1950-6-27","YYYYMMDD")), "Korean War (6/27/1950-1/31/1955)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1947-1-1","YYYYMMDD")), "Pre-Korean (1/1/1947-6/27/1950)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1941-12-7","YYYYMMDD")), "World War II (12/7/1941-12/31/1946)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1941-12-7","YYYYMMDD")), "Merchant Marine (12/7/1941-8/15/1945)",

IF(GTE(FORMAT_DATE({Service period from},"YYYYMMDD"),FORMAT_DATE("1917-4-6","YYYYMMDD")), "World War I (4/6/1917-11/11/1918)",

"Unknown")))))))))))
3

Amazing, thank you, it works.

Your observation is correct that a slight modification is required:

  • The formula currently does not incorporate a date, correct?
    From my understanding, the Monday Formula field is limited to displaying a single result. Hence, my thought process is to establish 2-3 formula fields and amalgamate the outcomes in a fourth one.
  • Concerning overlapping ranges - I intended to employ the same reasoning as in the previous “Formula does not account for a date.”
    I am utilizing the AI Formula builder, which admittedly does not match your level of expertise. I appreciate your assistance, Francis.