Help

Date Counting

Topic Labels: Dates & Timezones
Solved
Jump to Solution
1986 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Melanie_Lemnios
5 - Automation Enthusiast
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. 

Appreciate any and all advice!

 

 

1 Solution

Accepted Solutions
Soly
7 - App Architect
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 :

Soly_1-1674984201815.png

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).

Soly_2-1674984406276.png

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 🚀

 

 

See Solution in Thread

2 Replies 2
Soly
7 - App Architect
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 :

Soly_1-1674984201815.png

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).

Soly_2-1674984406276.png

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
5 - Automation Enthusiast
5 - Automation Enthusiast

This worked!  thank you so much!