Skip to main content
I have the above time stamps. What I need to do is calculate the hours/minutes from 8AM to the Start Date 1, as well as End Date 1 to 5pm. How can I accomplish this?

 

Hi ​@John_M,

Try these:


8:00 am to Start:

IF(
    HOUR(Start) + 8 < 8,
    0,
    (HOUR(Start) + 8 - 8) + (MINUTE(Start) / 60)
)

End to 5:00 pm

IF(
    HOUR(End) + 8 > 17,
    0,
    (17 - (HOUR(End) + 8)) - (MINUTE(End) / 60)
)

 

The +8 in the above formulas compensate for the timezone difference between UTC (which Airtable uses internally) and my local timezone. You will need to amend this based on the time difference from UTC where you are. 

 

The issue with the above is that you will have to change the formula if you have daylight savings to contend with. I’m not sure how you would do that, but I bet someone here could show you how. 

I got it. Use the SET_TIMEZONE() function to set the formula to match your date field and you’ll not have to use the adjustment or deal with daylight savings. 

The below will also return hours:minutes, as per your original post. 

IF(
    HOUR(SET_TIMEZONE(Start, 'America/New_York')) < 8,
    "0:00",
    CONCATENATE(
        HOUR(SET_TIMEZONE(Start, 'America/New_York')) - 8,
        ":",
        IF(
            MINUTE(Start) < 10,
            "0" & MINUTE(Start),
            MINUTE(Start)
        )
    )
)

 

That worked beautifully! Thank you sir!!

Thanks mate and no worries. Learned something new myself in the process. 

I just posted another topic for the ultimate goal this formula is leading to, if you’re interested in a real challenge… lol

Reply


Terms & Conditions