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!
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 🚀
This worked! thank you so much!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.