Adding a date to an IF statement

[AaronB]

Hey all, 

I could use some help and I'm hoping this isn't too difficult. I am trying to add a formula that compares an event date to the date of the next time change. If it is before the date of the time change, it runs one formula, if after, it runs another formula. I thought this would be simple enough but I keep getting an error. The formula I am using is below: 

IF(

      IS_BEFORE({Session Date},03/10/2024),

            "A working formula"),

IF(

      IS_AFTER({Session Date},03/10/2024),

           "Another working formula")

I know that the "working formulas" are correct because they work in other fields. I'm wondering if I need to format the date in a particular way or add a date field instead of using the specific date in the IF statement? Thanks in advance!

[pressGO_design]

You need to DATETIME_PARSE that time change date.

IF(

     IS_BEFORE(

          {Session Date},DATETIME_PARSE("03/10/2024", 'MM/DD/YYYY')), 

          "BEFORE",

     IF(

          IS_AFTER(

          {Session Date},DATETIME_PARSE("03/10/2024", 'MM/DD/YYYY')),

          "AFTER")

)

[Alexey_Gusev]

Hi,
You can use quoted date in ISO format, '2024-03-10'
also, you should adjust brackets
Here is your non-working formula:

IF( is_before_statement , 'result_before' ) , 
IF( is_after_statement , 'result_after' )

you can build it as nested IF:
IF( is_before_statement , 'result_before' , 
    IF ( is_after_statement , 'result_after' )
)

or, personally I like it more, as 2 independent IFs, just replace comma with ampersand
IF( is_before_statement , 'result_before' )  &
IF( is_after_statement , 'result_after' )

[AaronB]

Thank you for this, it was super helpful! I'm no longer getting an error, but it's also not returning a value at all. I know that the two timezone calculations work separately, so I'm assuming there must be a problem with how I have them nested or something. I'll include a screenshot of my formula! 

[Alexey_Gusev]

I think, you need to simplify your formula
put multiple IFs inside single DATEADD
instead of

IF(IS_BEFORE({Session Date), DATETIME_PARSE("03/10/2024", 'MM/DD/YYYY'),
IF(textCalendlyTimezone='America/Los_Angeles',
DATEADD(calculatedCalendlyStartTimeGMT,8, 'hours'),
IF(textCalendlyTimezone='America/Denver',
DATEADD(calculatedCalendlyStartTimeGMT, 7, 'hours'),
IF(textCalendlyTimezone='America/Chicago',
DATEADD(calculatedCalendlyStartTimeGMT, 6, 'hours'),
IF(textCalendlyTimezone='America/New_York',
DATEADD(calculatedCalendlyStartTimeGMT,5, 'hours')
)
)
)

turn them to switch:

IF(IS_BEFORE({Session Date},'2024-10-03',
DATEADD(calculatedCalendlyStartTimeGMT,
  SWITCH(textCalendlyTimezone,
  'America/Los_Angeles',8,
  'America/Denver',7,
  'America/Chicago', 6,
  'America/New_York', 5),
'hours'))

 Also, if you need just 2 possible options: Yes or No, you don't need to use both BEFORE and AFTER. You need it for 3 possible options 'before date' , 'equal to date' , 'after date'

Since you want to know whether you need to shift one hour according to a point in time,
it's OK to use only IS_BEFORE({date} , Yes - add one hour , No - don't add )
or use AFTER to substract. But not both.

You can cut out half of your formula.
Thus, you get:

DATEADD(calculatedCalendlyStartTimeGMT,
  SWITCH(textCalendlyTimezone,
  'America/Los_Angeles',8,
  'America/Denver',7,
  'America/Chicago',6,
  'America/New_York',5) 
    -IF(IS_AFTER({Session Date},'2024-03-10 00:00'),1,0),
'hours')

You can also use trick: IF( logical_statement , 1 , 0 ) can be replaced with (statement)
in math formula, TRUE will be converted to 1 , FALSE to 0
Also, last replace to  make it simple and understandable

DATEADD(calculatedCalendlyStartTimeGMT,
  IS_BEFORE({Session Date},'2024-03-10')+
    SWITCH(textCalendlyTimezone,
    'America/Los_Angeles',7,
    'America/Denver',6,
    'America/Chicago',5,
    'America/New_York',4),
'hours')

i would think to add default value in SWITCH after ',4
for the case of failed timezone. but that's another story...

[pressGO_design]

Last night I had an epiphany as I was trying to fall asleep. (If you're over 50, you understand this completely; if you're not, this is what happens when you're over 50: you forget why you walked into literally every room and right before you're falling asleep you remember AllOfTheThings.) But! The epiphany will only work if the calculateCalendlyStartTimeGMT is calculated using the Session Date field.

I'm imagining that your Session Date is just a date with no time, like 02/15/2024. Then the calculateCalendlyStartTimeGMT converts that Session Date a dateTime field, which in this case would be 02/15/2024 12:00am, and you then want to apply the time zone in the textCalendlyTimezone field to that time. 

If that's the case, why not let Airtable do the heavy lifting about the time change?

IF(textCalendlyTimezone='America/Los_Angeles', 
  SET_TIMEZONE(calculateCalendlyStartTimeGMT,'America/Los_Angeles'),
  IF(textCalendlyTimezone='America/Denver', 
    SET_TIMEZONE(calculateCalendlyStartTimeGMT, 'America/Denver'),
    IF(textCalendlyTimezone='America/Chicago', 
       SET_TIMEZONE(calculateCalendlyStartTimeGMT, 'America/Chicago'),
       IF(textCalendlyTimezone='America/New_York', 
          SET_TIMEZONE(calculateCalendlyStartTimeGMT, 'America/New_York')
)
)
)
)

That way, you don't have to change that hard-coded 10 March 2024 date every year.

In the Formatting pane of the formula field, just make sure that you UNcheck the "use the same time zone for everyone" bit.