# Re: Date Counting

Solved
844 0
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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.

1 Solution

Accepted Solutions
7 - App Architect

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 🚀

2 Replies 2
7 - App Architect

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 🚀

5 - Automation Enthusiast

This worked!  thank you so much!