Creating a Table Column based on a formula

Hello,

We are a nonprofit that offers emergency overnight shelter for the homeless in our community. Our shelter is open from 7pm to 7am every night and guests may check in until 3am.

Our "Service Date" for all occupants is the same for all entries between 7pm-3am on the same night including those who arrive after midnight.

I want to create a new formula-calculated column (SERVICE DATE) in the associated table for an entry form. The SERVICE DATE is not required on the entry form itself. The SERVICE DATE should default to the system Submission Date for entries until midnight but deduct 1 day for entries after midnight. Basically ...

IF Submission Time <= 3am

THEN

SERVICE DATE = Submission Date - 1 day

ELSE

SERVICE DATE = Submission Date

For example:

Submission Date: 2021-11-01 23:00 = SERVICE DATE: 2021-11-01

Submission Date: 2021-11-02 00:30 = SERVICE DATE: 2021-11-01

It's easy to do this in Excel but I've been trying without success to create a formula that does this in a Jotform Table. Can you help me create a column formula to do this?

Thanks!

Hello,

After playing around with the formulas in the table, I think the easier solution would be just add a condition to time in that if it's before 3:01 then the calculate the service date as Date - 1. This will get the service date entered into your table as the day you want.