IF Date field is after a certain time do this

[Jason_Peterson]

Hi,
I am looking for a way to build a formula that goes like this
If the date created+timestamp <= to 4PM that day then subtract 10 hours if not subtract nothing.

I am racking my head how to do this. Any help would be awesome!

[Justin_Barrett]

Welcome to the community, Jason! :grinning_face_with_big_eyes: To compare two datetime items, you’ll often use either IS_BEFORE or IS_AFTER. However, because in this case you simply want to see if the hour is before 4 p.m.—which would be 16:00:00 in 24-hour time—you can extract the hours in 24-hour mode, plus minutes and seconds, and compare that mashed value against 160000. Date addition/subtraction is done with DATEADD, using a negative value to subtract hours/days/months/etc.

In building the formula, you might’ve had the impulse to structure it similar to the way you wrote it out: begin with the IF portion, then have the True result be the 10-hour subtraction, and the False result be the unmodified time. However, both the unmodified and modified times would still need to be formatted for final output, which would lead to multiple instances of DATETIME_FORMAT in the formula. I try to avoid redundancy wherever possible, so I’d be inclined to begin with DATETIME_FORMAT, wrapping that around the IF function to determine which date gets formatted: the unmodified date, or the one 10 hours earlier.

In this example, I’m using a manually-created timestamp field named {Date Created}. Obviously replace that with your own field, and tweak the final formatting as you wish.

DATETIME_FORMAT(
    IF(
        VALUE(
            DATETIME_FORMAT(
                {Date Created},
                "Hmmss"
            )
        ) < 160000,
        DATEADD(
            {Date Created},
            -10,
            "hours"
        ),
        {Date Created}
    ),
    "L LT"
)

[Jason_Peterson]

Justin Thanks so much! This is exactly what I needed and thanks for explaining it to me!
Perfect!

[Jason_Peterson]

As it goes I asked for something that does not quite incompass what I want.

What I now realize I need is subtract 10 hours if date created is between 4pm to 7am the following day.

Building on what was given to by Justin above I did this, but something is wrong it just returns the same as the created date.

    DATETIME_FORMAT(
    IF(
      AND(
        VALUE(
            DATETIME_FORMAT(
                {Date Created},
                "HHmmss"
            )
        ) > 160000,
		VALUE(
            DATETIME_FORMAT(
                {Date Created},
                "HHmmss"
            )
        ) < 070000),

        DATEADD(
            {Date Created},
            -10,
            "hours"
        ),
        {Date Created}
    ),
    "L LT"
)

[Jason_Peterson]

Got It. :grinning:
This below worked. just did a nested if.
Thanks again for getting me on the right path

    DATETIME_FORMAT(
        IF(
            VALUE(
                DATETIME_FORMAT(
                    {Date Created},
                    "Hmmss"
                )
            ) > 160000,
            DATEADD(
                {Date Created},
                -10,
                "hours"
            ),
    	IF(
     VALUE(
                DATETIME_FORMAT(
                    {Date Created},
                    "Hmmss"
                )
            ) < 070000,
            DATEADD(
                {Date Created},
                -10,
                "hours"
            ),
            {Date Created}
        )),"L LT"
    )