Skip to main content

Hi,

 

I want to track employee annual leave via Airtable. They’ll input Date From & Date To (including time). There will be individual calculation fields for each leave type (e.g sick, annual leave etc) and an approval status. So this formula would be wrapped in IF conditions; IF Type = ‘Annual Leave” and Status = “Approved” this calculation will apply.

 

Formula needs to

  • only calculate weekdays (ignore weekends)
  • understand 1 day = 7.8 hours
  • only needs to count hours if the time differs between date from & date to fields.
  • Understand working hours are 8:30-5pm (time zones differ)

 

So outputs for the following would be;

17 June 2025 3:00pm → 17 June 2025 5:00pm → 2.0

​​​​​​​17 June 2025 3:00pm → 18 June 2025 3:00pm → 7.8

​​​​​​​17 June 2025 3:00pm → 18 June 2025 5:00pm → 9.8

​​​​​​​17 June 2025 3:00pm → 24 June 2025 3:00pm → 46.8

 

I’m open to doing this more simply via the base design if the formula is too difficult to write (e.g having the employee select from a drop down to say ‘full days’ or ‘partial days’ so a formula knows whether to just count weekdays or count hours - whatever is going to get the job day.

 

I also have A.i enabled (but I haven’t found it to be reliable in this use case)

Not sure if you’re still looking for support with this, but this formula might work for you:
 

(WORKDAY_DIFF({Date From},{Date To}) - 1) * 7.8 +

MOD(DATETIME_DIFF({Date To}, {Date From}, 'hours'), 24)

The only thing that makes me think this might not be what you’re looking for is that you indicated that 17 June 2025 3:00pm → 24 June 2025 3:00pm → 46.8. By my calculation, June 17-June 24 at the same time would be 5 days of leave (5*7.8=39), not 6 days. 

This formula will calculate leave rounded up to the nearest hour, I’m not sure if you want more accurate numbers than that. It also will not recognize if a start or end time is outside the limits you defined, but a double check of that could be managed elsewhere.

I posted a reply a little earlier that is waiting for approval so not sure if it will come through. If you are still looking for help with this, here is a more robust formula than what I previously posted. It’s a complicated calculation for sure!

 

IF(
  AND({Type} = "Annual Leave", {Status} = "Approved"),  IF(
    AND(
      NOT(IS_SAME({Date From}, {Date To}, 'day')),
      HOUR({Date From}) + MINUTE({Date From})/60 = HOUR({Date To}) + MINUTE({Date To})/60
    ),
    (WORKDAY_DIFF({Date From}, {Date To}) - 1) * 7.8,    IF(
      IS_SAME({Date From}, {Date To}, 'day'),
      MAX(0,
        MIN(22, HOUR({Date To}) + MINUTE({Date To})/60) -
        MAX(13.5, HOUR({Date From}) + MINUTE({Date From})/60)
      ),
      MAX(0, 22 - MAX(13.5, HOUR({Date From}) + MINUTE({Date From})/60)) +
      MAX(0, WORKDAY_DIFF({Date From}, {Date To}) - 2) * 7.8 +
      MIN(7.8, MAX(0, MIN(22, HOUR({Date To}) + MINUTE({Date To})/60) - 13.5))
    )
  ),  0
)

 

Terms & Conditions