Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: Automation is adding an extra day when creating a new record

Solved
Jump to Solution
1006 0
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeCraycraft
5 - Automation Enthusiast
5 - Automation Enthusiast

I've created a task list. When 'Done" is clicked, the "Date Completed" is updated and the "Next Due" is updated based on a formula dependent on the 'Freq'. All this works fine.

I then have an automation that runs to create a new record with a 'Due' date populated with the previous records 'Next Due'

The automation runs fine except in the evenings where it adds a day? During the day it is fine. 

You an see in the attached, "IG New Messages" was clicked 'Done" on 1/10, 'Date Completed' was 1/10, 'Next Due' was 1/11 but then the automation changed the new records 'Due' to 1/12

It is like there is a time zone issue somewhere but I can't figure it out so any help is greatly appreciated.

1 Solution

Accepted Solutions
MikeCraycraft
5 - Automation Enthusiast
5 - Automation Enthusiast

TL;DR: It appears that the 'Date' field type defaults to GMT, unless you turn on the 'Include Time" function and 'Use the same time zone for all collaborators' and then select the desired time zone. However, the time zones are still not respected unless you use the 'include time' in the formula formatting results. With my abilities I can't find a way for the formulas/automation to respect the time/time zone without displaying the time, which makes it messy looking but at least it is functional.

Screen Shot 2025-01-14 at 7.43.39 PM.png

 

OK, so I have a mixed solution. Thank you @ATSolutionist as the Error code fixed the annoyance and the hours code worked too. I saved it in the base incase I need to use it somewhere else. 

As @DisraeliGears01 mentioned, I turned on the show time zone and noticed that the "Date" record type is basically hard coded to UTC/GMT and the only way to get around it is to turn on the time format.

Screen Shot 2025-01-14 at 2.43.43 PM.png

Even with all fields on EST, the error occurred again tonight after 7pm.
Screen Shot 2025-01-14 at 7.11.09 PM.png
I edited the automation to be based off the 'Hours Next Due' and it still skipped from 1/15 to 1/16.

Only after I turned on "Include Time" in the formatting of the formula was there finally resolve and the adding a day was removed. This last example, was based off the original 'Next Due' field. 
Screen Shot 2025-01-14 at 7.33.00 PM.png


See Solution in Thread

7 Replies 7
ATSolutionist
6 - Interface Innovator
6 - Interface Innovator

Hi Mike - 

Is the date completed field formatted to also show time? Have you tried turning the include time off? 

Does any of your formulas powering the Next Due field have date configurations? 

If you'd like to share the formula or a snap shot of the automation, I'd be happy to take a closer look. There's also a formula function to help you override the pesky ERROR messages. 

 

 

Katelyn
info@consultasolutionist.com

Thank you for the quick reply Katelyn.

The completed field does not have a time included. I did turn on the "Use the same time zone for all collaborators" to troubleshoot but turned it off and it still just added a day.
Screen Shot 2025-01-13 at 11.23.14 PM.png

Same with the formula field. I did again turn off the same time zone function but still added a day and here are the formulas
Screen Shot 2025-01-13 at 11.26.30 PM.png

I apologize as I am a pharmacist by trade and this is for a cancer charity I founded so I am not sure what is the best way to share the automation but
Screen Shot 2025-01-13 at 11.30.05 PM.pngScreen Shot 2025-01-13 at 11.30.32 PM.png

I did notice that the same "task" run during the day and then run just now, the one late in the evening is when the extra day is added to the 'next due' at 10:39am it changed from 1/12 to 1/13 appropriately but at 11:38PM it changed from 1/13 to 1/15. I tired to go back and there are automations that ran at 6:58pm and were fine but then after 7pm is when it adds a day and it is for all formulas, add 3 days changes to 4 days, etc.
Screen Shot 2025-01-13 at 11.37.51 PM.pngScreen Shot 2025-01-13 at 11.39.11 PM.png
 I sincerely appreciate any isight as I'm self-taught and have topped out my learning curve. haha

ATSolutionist
6 - Interface Innovator
6 - Interface Innovator

Hi Mike - 

Try working with hours instead - I've typed a formula code for you below : 

 

IF(Done=FALSE(),BLANK(),
  IF(Freq='Daily', DATEADD({Date Completed},24,'hours'),
    IF(Freq='3Days',DATEADD({Date Completed},SUM(24*3),'hours'),
      IF(Freq='Weekly',DATEADD({Date Completed},SUM(24*7),'hours'),
        IF(Freq='Monthly',DATEADD({Date Completed},SUM(24*28),'hours')
        )
      )
    )
  )
)

 

 if you still have the formula formatted to date, it shouldn't matter if the time is included. If you do add the time it will default to the time the last task was complete. 

Katelyn
info@consultasolutionist.com
DisraeliGears01
7 - App Architect
7 - App Architect

This is absolutely a timezone issue, Airtable loves to convert everything to UTC/GMT. Have you tried turning on all the time fields, setting your timezone to the appropriate one, and then just ensuring your automation/formula fields format away the time part of your datetime string? That usually solves this problem for me.

MikeCraycraft
5 - Automation Enthusiast
5 - Automation Enthusiast

TL;DR: It appears that the 'Date' field type defaults to GMT, unless you turn on the 'Include Time" function and 'Use the same time zone for all collaborators' and then select the desired time zone. However, the time zones are still not respected unless you use the 'include time' in the formula formatting results. With my abilities I can't find a way for the formulas/automation to respect the time/time zone without displaying the time, which makes it messy looking but at least it is functional.

Screen Shot 2025-01-14 at 7.43.39 PM.png

 

OK, so I have a mixed solution. Thank you @ATSolutionist as the Error code fixed the annoyance and the hours code worked too. I saved it in the base incase I need to use it somewhere else. 

As @DisraeliGears01 mentioned, I turned on the show time zone and noticed that the "Date" record type is basically hard coded to UTC/GMT and the only way to get around it is to turn on the time format.

Screen Shot 2025-01-14 at 2.43.43 PM.png

Even with all fields on EST, the error occurred again tonight after 7pm.
Screen Shot 2025-01-14 at 7.11.09 PM.png
I edited the automation to be based off the 'Hours Next Due' and it still skipped from 1/15 to 1/16.

Only after I turned on "Include Time" in the formatting of the formula was there finally resolve and the adding a day was removed. This last example, was based off the original 'Next Due' field. 
Screen Shot 2025-01-14 at 7.33.00 PM.png


DisraeliGears01
7 - App Architect
7 - App Architect

If the time display is really bothersome, I think you could add a formula field running DATETIME_FORMAT( {Due}, 'LL') and hide the original due field, which will strip away the time element and just display month/day/year. 

Timezone handling is one of the worst aspects of Airtable tbh.

Great idea. I didn't think about that. I created the new field and changed the format a little so DD/MM/YY and it looked great as I hid the other fields. The only issue is that I can change the original 'Due' date since it is a date. Change it to skip a weekend if needed, or change to the 5th of the month instead of the 3rd. 

With the formula field I realized it looked better but I couldn't just click and change. 

Also, I marked this as resolved but couldn't mark both of your solutions so I tried to summarize it.

Thank you @DisraeliGears01 and @ATSolutionist as I so appreciate your help.