Hi AirTable Community, 

I’ve been struggling with WORKDAY_DIFF formula. 

My team tracks reports using Airtable. We set an attendance date (AD) and a report type that are set/selected. Based on the report type, I have a formula that will add a certain number of days to the AD to calculate the draft due date (DDD). There is also an option to assign a due date should we need to override the calculated date. 

Formula as follows:

IF({Assigned Draft Due Date}, {Assigned Draft Due Date}, 

SWITCH({Report Type / Tasks}, 

'ACM - ASMP', WORKDAY({Attendance Date}, 5), 

'ACM - Audit/Survey Report', WORKDAY({Attendance Date}, 8),    etc., etc. for every report type. 

My operations team also want a ‘Days Overdue Field’ so that they can quickly see if a report is overdue. 

The WORKDAY_DIFF field I know includes both the start date and the end date in its calculations, I can negate this by minusing 1 day in the formula. 

But it seems no matter what settings I use - I tried setting ‘Use same timezone for all colaborators’ both on and off, or if I use a separate field to convert to Australia/Brisbane Timezone and calculate using that field, it still doesn’t claculate correctly.

I’ve even tried converting to text field using DATE_PARSE()

Convert AD to Text:

DATETIME_FORMAT({Attendance Date},'DD/MM/YYYY')

Convert that text to a date & set as Aus/Bris timezone: SET_TIMEZONE(DATETIME_PARSE({AD to Text String}, 'DD/MM/YYYY'),  'Australia/Brisbane' )

Use that resulting date to calculate the Draft due date and then use that date with the WORKDAY_DIFF formula and it still does not calculate it accurately until it ticks over to 10am (which would be midnight GMT). 

I’m worried I’ve overcooked this and would appreciate any ideas or help, please!