Welcome to the community, @Arnold_Muganhu! :grinning_face_with_big_eyes: Try this and see if it works for your needs:
IF(
TODAY() < {Lease Start Date},
"Inactive",
IF(
AND(
TODAY() >= {Lease Start Date},
TODAY() < {Lease Termination Date}
),
"Active",
"Expired"
)
)
Note: The TODAY() function returns the a datetime that represents midnight on the current date in GMT, not your local timezone. Depending on your local timezone’s relationship to GMT, the date returned by TODAY() might change either before or after the date changes locally for you. For example, TODAY() returns December 28th for me, but it’s only 8:30 pm on Dec 27th right now (Pacific time). In many cases this difference can be corrected by using the SET_TIMEZONE() function, but the datetime returned by TODAY() is one of the few cases where SET_TIMEZONE() has no effect.
A more reliable comparison can be made using the NOW() function, which reports the current date and time. Like TODAY(), it’s also natively based on GMT, but the SET_TIMEZONE() function does work to adjust it to your local timezone (see options here). Here’s an example of how I’d change the above formula to use NOW() with the corrected time for my local timezone (US Pacific):
IF(
SET_TIMEZONE(NOW(), "America/Los_Angeles") < {Lease Start Date},
"Inactive",
IF(
AND(
SET_TIMEZONE(NOW(), "America/Los_Angeles") >= {Lease Start Date},
SET_TIMEZONE(NOW(), "America/Los_Angeles") < {Lease Termination Date}
),
"Active",
"Expired"
)
)
