Creating a Table Column based on a formula

  • Profile Image
    SMUN_ITadmin
    Asked on November 02, 2021 at 01:12 PM

    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!

  • Profile Image
    parker
    Answered on November 02, 2021 at 04:33 PM

    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.


    1635885173_6181a075ddccd_