Date Counting

[Melanie_Lemnios]

Hello!

I have a list of records that all have a start date and end date column for a number of employees.  I am wondering if there is a way to calculate the number of employees on a given day.  All the employees have different start and end dates.  So for example I want to know the total working on March 1 vs March 7.  I know there is a way to do this in excel using an IF/AND formula but can't figure out how to do it in Airtable. 

Appreciate any and all advice!

[Soly · Accepted solution]

Hello @Melanie_Lemnios ,

The formula you may use for your employee counter {Employee Count} is :

IF(AND({Start Date} < {Given Day}, {End Date} >= {Given Day}), 1, 0)

It puts 1 if the employee is present and 0 if he was absent that day, based on the start and end date.

Which will give you this result :

To use it, you have to change the dates in the {Given Day} field to the one you want to test. In your case, set the given day date to March 1 and look at the bottom of the window, Airtable calculate the sum for you and shows 3 employees were working on the given day, (1/16/2023 in my table).

So you simply have to change the date of the {Given Date} to count them.

Here is a great article that can be helpful to use as reference.

I hope that helps 🚀

[Melanie_Lemnios]

This worked!  thank you so much!