Help

Re: Workday formula returning the wrong dates

527 1
cancel
Showing results for 
Search instead for 
Did you mean: 
rseltzer
4 - Data Explorer
4 - Data Explorer

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
rseltzer_2-1706909236983.png

 


 

What am I doing wrong here? Or is there a better way to to do this? I've been at it for hours. 

3 Replies 3

Hm that's weird, this seems to work fine for me where the formula is:

WORKDAY(
  {Campaign Launch},
  -13
)

Screenshot 2024-02-03 at 2.15.46 PM.png
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?

rseltzer
4 - Data Explorer
4 - Data Explorer

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. 

rseltzer_0-1707075087471.png

 

Ah, to do that you'll need to turn on "Include time" for your date field:

Screenshot 2024-02-05 at 5.37.17 PM.png

You'll then get the option to set the timezone for that date field