Help

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.

Date Counting

Topic Labels: Dates & Timezones
Solved
Jump to Solution
2273 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!