Strip time from date and add a new time

[Mike_Shaw1]

(i edited this to make more sense hopefully🤪)

I want to create a new date and time, by stripping the time from a date, add a certain number of days, then add a duration field back as a new time to the date such as :

1/3/24 11:00 am   to   1/3/24    add 126 days becomes   5/8/24  then add 15 hours to get the result of 5/8/24 3:00 pm

here is my formula:

DATEADD(DATETIME_FORMAT(DATESTR(DATEADD(DATESTR({Initial Date}),{Days to Add},'d')),'MM-DD-YYYY'),{Duration},'s')  

However when I use DATEADD to add the duration as a time, it assumes the starting time of 18:00 / 6pm  AND cuts the days short by 1 so when I add the duration it adds it to the 18:00 / 6:00 pm for some reason not from 0:00 /12:00 am and is the day prior so 5/7/24 not 5/8/24

If I just use this it gets me the right date with no time: 

DATESTR(DATEADD(DATESTR({Initial  Date}),{Days to Add},'d'))

 FYI the initial date field is formatted for the right time zone etc

ANY ideas??

[Mike_Shaw1]

FYI my work around is to compensate by subtracting 64,800 sec and adding 1 day for the weirdness but it seems so unnecessary :

DATEADD(DATETIME_FORMAT(DATESTR(DATEADD(DATESTR({Initial Date}),{Days to Add}+1,'d')),'MM-DD-YYYY'),{Duration}-64800,'s')

[kuovonne]

You are probably dealing with a timezone issue. Airtable stores date/time values in GMT, regardless of the displayed timezone. What is your timezone offset from GMT? Based on the time you are adding and subtracting, I’m guessing your timezone offset is 6 hours. 

You can try simply adding the timezone offset in one step instead of adding and subtracting the seconds and adding the day. Keep in mind that your timezone offset will change as you move between standard time and daylight savings time. 

[Mike_Shaw1]

Hey thanks for the input, I was having some issues with my formula triggering when it was in the evening closer to midnight, so I revamped it and tweaked it for 3 hours last night through midnight mst until it finally I think it is good now but I mean I tried many things to simply and different order of things and this is what worked in the end,  what are your thoughts on this bad boy, can it be simplified and still work?

FYI I have no idea how to format and add this in like you guys do other than a straight-line but here is my attempt: edit how does this look?

DATETIME_FORMAT(
  SET_TIMEZONE(
    DATEADD(
      DATETIME_FORMAT(
        SET_TIMEZONE(
          DATEADD(
            DATESTR(
              {Initial Email Trigger Send Date}
            ),
            {FU Days Since Initial Email},
            'd'
          ),
          'America/Denver'
        ),
        'MM/DD/YYYY'
      ),
      {FU SMS Send Trigger Time},
      's'
    ),
    'America/Denver'
  ),
  'MM-DD-YYYY h:mm'
)​