Skip to main content

Hello, 

I have an airtable app that supports our HR and Finance teams.  As part of the build, I am looking to reference the employee ‘start date’ and ‘end date’ to determine how many days, or workdays that employee worked in a specified month (assume each month starts on the 1st and ends on the last day 30/31) 

Example fields:
Start date: 01-10-25
End date: 01-25-25
Month Start: 01-01-25
Month End: 01-31-25

 

The month start and ends fields could be done without referencing the fields if needed and handled within the calc using datetime parse/datetimeformat or the like.

 

In many examples the end date of the employee will be after the month end but have kept the duration short for this example.

In theory, a simple formula like the below should work, but I’ve tried so many different options of doing this and the formulas keep returning a 0 or error. The fields above are all calculated as date fields.

WORKDAY_DIFF(
 MAX({Month Start}, {Start date}),
 MIN({Month End}, {End date})
)

I’d expect a working formula to return me a number such as 16 days, or 11 workdays.

This started as part of a larger formula and the rest has been solved but this is the last part giving me trouble! :melting_face: 

Anyone have any tips for why this isn’t working or other suggestions for other methods to try. 

After my known formulas weren’t working I also tried many AI generated suggestions and none are working. 

Thank you! 

The issue is that the MIN and MAX functions are expecting only an integer (1,2,3,4, etc) but you’re providing them with a date string. 

I think you need to use nested IF statements with IS_BEFORE and IS_AFTER to determine which date to utilize in your WORKDAY_DIFF calculation.

Someone may drop in with the correct formula, but for some reason my brain just doesn’t want to get there at the moment. One way to work through this I find helpful is to do each component of the calculation in it’s own formula field and then combine them together afterward.

Does this look right?

 

WORKDAY_DIFF(
  DATETIME_PARSE(
    MAX(
      DATETIME_FORMAT({Month Start}, 'x') + 0, 
      DATETIME_FORMAT({Start date}, 'x') + 0
    ),
    'x'
  ),
  DATETIME_PARSE(
     MIN(
      DATETIME_FORMAT({Month End}, 'x') + 0, 
      DATETIME_FORMAT({End date}, 'x') + 0
    ),
    'x'
  )
)

The issue is exactly what DisraeliGears describes, and so the idea is to convert the date into a number that MAX and MIN can use, then format it back into a date so that WORKDAY_DIFF works fine

We do this by using ‘DATETIME_FORMAT’ to convert it to the unix timestamp format ('x'), which gives us the date as the number of milliseconds since the epoch.  That way, MAX and MIN can properly compare the two values numerically

After choosing the latest of the two start dates and the earliest of the two end dates, we convert them back into datetime objects using DATETIME_PARSE(..., 'x'), and finally calculate the number of working days between them with WORKDAY_DIFF

Feels like there might be a cleaner way to do it, but eh it works 

It works! Amazing! Thanks you ​@TheTimeSavingCo & ​@DisraeliGears01!  

This makes sense and a bit of what I was expecting needed to be done (convert to a string then back to date) but couldn’t wrap my head around it.  I also wasn’t familiar with using the “x” to convert to the unix timestamp so great to know!! 

Thank you so much - knew there had to be a way to solve this so I’m so appreciative. 🙏

​@TheTimeSavingCo When I was typing my response I originally wrote “Adam from TimeSaving may drop in with the correct formula...” and then I was like “Nah, I shouldn’t call him out that directly, I’ll just put someone” 🤣

​@DisraeliGears01 😂

Terms & Conditions