- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 13, 2025 04:59 PM
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.
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 14, 2025 04:53 PM
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.
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.
Even with all fields on EST, the error occurred again tonight after 7pm.
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 13, 2025 07:14 PM - edited ‎Jan 13, 2025 07:15 PM
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.
info@consultasolutionist.com
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 13, 2025 08:55 PM
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.
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
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
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.
I sincerely appreciate any isight as I'm self-taught and have topped out my learning curve. haha
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 13, 2025 10:55 PM - edited ‎Jan 13, 2025 10:56 PM
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.
info@consultasolutionist.com
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 14, 2025 10:56 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 14, 2025 04:53 PM
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.
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.
Even with all fields on EST, the error occurred again tonight after 7pm.
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 14, 2025 05:07 PM - edited ‎Jan 14, 2025 05:08 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Jan 14, 2025 05:31 PM
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.
