The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Jan 29, 2023 12:20 AM
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!
Solved! Go to Solution.
Jan 29, 2023 01:33 AM - edited Jan 29, 2023 01:39 AM
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 🚀
Jan 29, 2023 01:33 AM - edited Jan 29, 2023 01:39 AM
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 🚀
Jan 29, 2023 04:29 AM
This worked! thank you so much!