Help

Kindly assist with formula for where a lease is "In active" before Lease start date, "Active" after start date but before termination date, and "Expired" after termination

1125 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Arnold_Muganhu
4 - Data Explorer
4 - Data Explorer

Hello,
Kindly assist with formula for the following lease validity scenario. If today()) is before {lease start date} it is “In Active” If however it is after {Lease start Date} and before {Lease termination Date} It is "Active " Otherwise it is “Expired”

Regards Arnold

2 Replies 2

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"
    )
)
Arnold_Muganhu
4 - Data Explorer
4 - Data Explorer

Thanks Justin for your most prompt assistance… The second option worked. Thanks once again