Hi - desperately need help in figuring out what in the world is not working.
I am creating a production timeline where the campaign launch is the starting point. From there, I want to count and report certain milestone dates bases on a typical timeline. These milestones are working days (M-F) so not a normal "count backwards". I have to do this for multiple types of campaigns, so instead of making stagnant formulas I'm referencing dates on another table. yes, I looked in to automations and record templates, but they don't allow for relative dates so this is the workaround.
Example) Campaign 1
- Milestone 1: Day -13
- Milestone 2: Day -10
- Milestone 3: Day -7
- Milestone 4: Day -2
- Milestone 5: Day -1
- Campaign Launch: Day 0
Example) Campaign 2
- Milestone 1: Day -17
- Milestone 2: Day -15
- Milestone 3: Day -12
- Milestone 4: Day -5
- Milestone 5: Day -3
- Campaign Launch: Day 0
I've been trying to use the formula workdays to accomplish this while referencing the milestone dates, but every time I use it the formula returns weekend days. And to boot, when I try to check my formulas using a reverse weekday (take my output and check if its a weekday or weekend) it even spits out that a weekend is a week day (i.e. the workday formula returns a Sunday, says it's weekday number is 1 instead of 0).
My formula for reference:
WORKDAY({Campaign Launch},{Milestone #1 (from Campaign Timetable)})
Here's an example)
- Milestone #1 (M#1 Date) should be -13 weekdays before the Campaign launch.
- M#1 Weekday # shows the correct day of week the M#1 Date should be, but M#1 Date is obviously not right (4/7 is a Sunday, which should be 0 not 1).
- When I reverse look up the M#1 Date using a WEEKDAY({M#1 Date}), the formula returns the correct day of week the M#1 Day should be, but not what referenced date actually is.
- So if you look at 4/2, the WEEKDAY() should be 2 (based on how Airtable has Sunday=0 and Saturday=6), but that formula returns 3
What am I doing wrong here? Or is there a better way to to do this? I've been at it for hours.
Hm that's weird, this seems to work fine for me where the formula is:
WORKDAY(
{Campaign Launch},
-13
)
And here's a link to the base in case it helps with troubleshooting
Could you turn on the timezone display for both of the date fields to see whether there might be an issue there maybe?
It's definitely a time zone issue. I checked and turned it all on to GMT and now it shows the correct days. Thank you! Was losing my mind.
I guess my next question is there a way to set the Timezone for a Date field? I've looked in to this before and only see it possible to change on Formula fields.
Ah, to do that you'll need to turn on "Include time" for your date field:
You'll then get the option to set the timezone for that date field
