Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Items on concurrent date

Topic Labels: Formulas
894 0
cancel
Showing results for 
Search instead for 
Did you mean: 
the_ryno
4 - Data Explorer
4 - Data Explorer

Firstly I love airtable and it has provided a ton of solutions to our company.

I am having trouble solving this. I would like to know, how many projects were running concurrently on the same date.

So for example:

August 1, we had item A and item B going, on august 3 we started item C, and august 4 we started items D, E, and F. On Aug 5, we finished item A and Item C. I would like to see the data like this. Each one of these items will get a start date and an end date.

Aug 1: Item A and Item B (2)
Aug 2: Item A and Item B (2)
Aug 3: Item A, Item B, and Item C (3)
Aug 4: Item A, Item B, Item C, Item D, Item E, and Item F (6)
Aug 5: Item B, Item D, Item E and Item F (4)

I have been using WORKDAY_DIFF currently to count how many days these items ran for.

The end goal is I would like to divide an overhead cost to into each items depending on how many items are currently running

so using the same example

Aug 1: 2 1000/2 = 500
Aug 2: 2 1000/2 = 500
Aug 3: 3 1000/3 = 333
Aug 4: 7 1000/7 =142
Aug 5: 5 1000/5 = 200

Item A: ran through Aug 1 through Aug 4 (4) so it had a cost of 500 + 500 + 333 + 142 = 1475
Item B: ran through Aug 1 through Aug 5 (5) so it had a cost of 500 + 500 + 333 + 142 +200 = 1675
Item C: ran through Aug 3 through Aug 5 (5) so it had a cost of 333 + 142 +200 = 675
Item 😧
Item E:

So this process would be on going. I would manually enter the items and start and finish dates.

Ok let me know whatever other info yall need. I am pretty new to formulas.

0 Replies 0