Rollup formula IF date is between a start date and end date

[Ben_Crouch]

Hoping for some help!

I have a column with a date in, and two other columns, one with a start date and one with an end date,

I want to do a conditional / IF rollup that brings me through a value if the date in question falls between the start date and end date in my other columns

grateful for any help!

[Ben_Crouch]

Sorry to be a bit cleaer, I have Table A that has multiple entries in it related to each customer and the money we have charged them. this table has the date we charged the customer in it as a field

I then have Table B where I have each customer and month listed as an entry with other data about that customer and I want to rollup and match what I charged that customer in that month. This is the table that has the fields with the start dates and end dates in.

[Jeremy_Oglesby]

Hi @Ben_Crouch

I’m still not really clear on what the data in your tables represents, which makes it hard to envision the solution.

Based on what you’ve described, I’d imagine you have a table for Customers (a record for each customer), and a table for Charges (a record for each charge), but this “Table B” you are describing doesn’t sound like it fits either one of those. What kind of data are you wanting each record in “Table B” to represent?

[Ben_Crouch]

Very fair - I wrote this use case in a rush and will take a little more time and care over it @Jeremy_Oglesby

My tables (amongst others):

1. Customers (A record per customer)
2. Charges (a record per charge per customer, each with a date the charge was raised and an amount, linked to the customer table)
3. Charge months (A record created per month, per customer. So each customer has a unique record per month of the year). The table is configured by a formula for the unique field, which ends up creating a name along the convention of: ‘customer - month’. there is only ever one ‘charge month’ per month, per customer. The charge month is also linked to the customer. Each record also has a field where the start of the month is entered as a date field and the end of month is entered as a date field.

In table 3 (charge months) I would like to reference table 2, and for each record (which represents a month of the year per customer) any charges raised in that month (as noted from the charge date in table 2), I would like all of those charges summed and rolled up and attributed to that ‘charge month’ if the charge dates in question fall between the start and end date of that record/month in table 3.

If this is still not clear enough I thank you for your patience and can provide screenshots etc.

thanks!

[Jeremy_Oglesby]

Ok, thanks @Ben_Crouch, I think I’ve got it now.

I think you’d be doing yourself a favor to link your Charges to Charge Months rather than directly to Customers. You can still pull the Customer into the Charge through the Charge Month.

By letting Charge Month act as a mediator between Customer and Charge, the problem largely solves itself. The Charge Month now just needs to roll-up its own Charges to get a sum.

Is there any reason establishing your linked relationships in that way wouldn’t work for your use-case?

[Ben_Crouch]

Hi Jeremy,

I’m going to look into that Monday. I think the drawback there is we would need to manually link each charge to the appropriate charge month. Charges are populated automatically from our invoice software, so someone would regularly need to go to the table and look each charge up and link it. I hate having humans in the loop :slightly_smiling_face:

Will assess Monday and report back.

Thanks
Ben

[Philo]

Hey, did you figure it out please?