Creating Crazy Formula for Billing

[Chris_M]

Hi there!
I need some help creating a valid formula for billing. This formula I’m posting is ugly and insane, and it’s a non-workable rough idea of what I’m needing. I’m hoping I can start a conversation to help me identify ways to either make extra columns in airtable, or identify whether this formula could actually work, or if I’m crazy for trying to make it this complicated. Here goes lol…

IF({LAST BILLING DAY}>{Date Received}>{FIRST BILLING DAY}
AND {Pull Date}>"!=blank()"
AND {LAST BILLING DAY}>{Pull Date}>{FIRST BILLING DAY},
DATETIME_DIFF({Pull Date},{Date Received},‘days’),
IF({FIRST BILLING DAY}>{Date Received}
AND {Pull Dates}=blank(),
DATETIME_DIFF({LAST BILLING DAY},{FIRST BILLING DAY},‘days’),
IF({FIRST BILLING DAY}>{Date Received}
AND {Pull Dates}> “!=blank()”,
DATETIME_DIFF({Pull Dates},{FIRST BILLING DAY},‘days’),
DATETIME_DIFF({LAST BILLING DAY},{Pull Dates},‘days’))))

To explain a little, a “pull date” is when it’s delivered.
I’m wanting to create a simple formula so that the output is simply the number of days the product was in the warehouse for the month, I would only adjust the FIRST BILLING DATE and LAST BILLING DATE each month. Thanks for anyone that would want to attempt this!

UPDATE:
I found a formula that works for all items that haven’t been delivered yet:
IF(AND({LAST BILLING DAY}>{Date Received},{Date Received}>{FIRST BILLING DAY},{Pull Date}=0), DATETIME_DIFF({LAST BILLING DAY},{Date Received},‘days’),“error”)

But I needed to have another column to identify the delivered items:
IF({NOT DLVD BILLING}=“error”, DATETIME_DIFF({Pull Date},{Date Received},‘days’),BLANK())

But this second formula I need to change it so it accounts for items that are received or delivered “outside” of the billing month. Thanks again!

[Chris_M]

Hi all,
I figured this crazy formula out, I couldn’t do Nested IF formulas for this, I had to create 23 different tabs that included IF_BEFORE – IF_AFTER – IF(AND()) – SWITCH() and some others. But it works after a month of trying to figure it out :slightly_smiling_face:

[Clockworks_Team]

Wow, that is crazy! I wonder if there’s an easier way than having to use Airtable’s relational database structure to solve for this. Have you tried the pivot block + a new view on the data so you don’t have to create 23 different tabs? Anyway, I’m glad you figured out a solution :slightly_smiling_face:

[Chris_M]

I’d love to collaborate more about this, making an easier formula would definitely help some people attempting the same thing. I briefly looked at a Pivot Table and don’t see it helping much, but I might need to possibly look into it more.
Here is a little more about what I was solving for:

I manage an airtable for my work where we receive and deliver shipments of product, where my end result is wanting to have a column automatically calculate how many days the shipment was in the warehouse, for any billing month. Here were
my variables:

Input Variables:

Received before the billing month

Received within the billing month

Received after the billing month

Delivered before the billing month

Delivered within the billing month

Delivered after the billing month

First day of billing month (I have to manually change)

Last day of billing month (I have to manually change)

End Result:

Each shipment calculates how many days the shipment was in the warehouse during the billing month (I ended up making each cell blank() if the shipment wasn’t in the warehouse during the billing month)

[Clockworks_Team]

It makes sense what you’re trying to do. This is definitely something that a pivot table could help with – I’d try that. I don’t have your Base structure perfectly in my head, but I would start by opening the Pivot block and choosing the dates that you want to aggregate, and then select ‘group by month’. This way you don’t have to have calculate each of these columns for start / end of month and days in months. You may have to reformat your inventory table to make this pivot table to work, but my bet is this will keep you saner in the long run.

This is not a perfect article because it’s a tutorial for pivot tables in Excel, but the principle of the pivot table is the same for Airtable, so I’d use this for guidance: https://trumpexcel.com/group-dates-in-pivot-tables-excel/.