Forum Discussion

sconway1985's avatar
sconway1985
Copper Contributor
Mar 17, 2023
Solved

Formula for Dynamic DST Time field

I am trying to write a formula that allows me to reference a time field which is in UCT, to translate it to local time, but also work dynamically with DST. The formula below is what I have been using...
excel
Formulas and Functions
  • sconway1985's avatar
    sconway1985
    Mar 23, 2023
    =IF(AND([@[Shift Start]]>=DATE(YEAR(NOW()), 3, 1) + (14 - WEEKDAY(DATE(YEAR(NOW()), 3, 1), 1))+TIME(2, 0, 0), [@[Shift End]]<DATE(YEAR(NOW()), 11, 1) + (8 - WEEKDAY(DATE(YEAR(NOW()), 11, 1), 1))+TIME(2, 0, 0)), [@[Shift Start]]-(5/24), [@[Shift Start]]-(6/24))


    This is the fix
sconway1985's avatar
sconway1985
Copper Contributor
Mar 23, 2023
=IF(AND([@[Shift Start]]>=DATE(YEAR(NOW()), 3, 1) + (14 - WEEKDAY(DATE(YEAR(NOW()), 3, 1), 1))+TIME(2, 0, 0), [@[Shift End]]<DATE(YEAR(NOW()), 11, 1) + (8 - WEEKDAY(DATE(YEAR(NOW()), 11, 1), 1))+TIME(2, 0, 0)), [@[Shift Start]]-(5/24), [@[Shift Start]]-(6/24))


This is the fix

Resources