Support Request: Formula for a Date Range within a Date Range as a Percent of Time

Jamie Dent

Here is my dilemma -

We have many projects that fall within a date range over multiple years. We have a new report requested that uses the date range 7/1/22-6/30/23. We need to report the portion of the total award that falls within that time range out of the total time range.

We would greatly benefit from a formula that can extract this number.

Out of the date range 8/1/22-7/31/26, we want to know the portion of the date range that is 7/1/22-6/30/23 and the amount of the total award for that period of time.

Out of 48 months (8/1/22-7/31/26), 11 months of that range falls within the date range 7/1/22-6/30/23 is ~23% of the total time of the total date range. Taking .23 X 2,006,906 = ~461,588. For the next period, 7/1/23-6/30/24, that will be 12 months of that range and will be 25% X the total award amount which equals ~501,726 and so on.

Thanks in advance for the discussion!

Jamie

Paul Newcome

You would subtract the MAX start date from the MIN end date and then divide that by the total number of days then multiply that by the dollar amount.

=((MIN(DATE(2023, 06, 30), [End_Date]@row) - MAX(DATE(2022, 07, 01), [Start_Date]@row)) / ([End_Date]@row - [Start_Date]@row)) * [Total_Award]@row

Paul Newcome

You would subtract the MAX start date from the MIN end date and then divide that by the total number of days then multiply that by the dollar amount.

=((MIN(DATE(2023, 06, 30), [End_Date]@row) - MAX(DATE(2022, 07, 01), [Start_Date]@row)) / ([End_Date]@row - [Start_Date]@row)) * [Total_Award]@row

Jamie Dent

Paul,

Just awesome that worked! I placed that into a cell on the row and got a number I verified by longhand. See image below in the far right column.

Taking this to the next step, how to run this formula on another sheet, using a SUM type formula to get a total amount of all projects that meet the date criteria you figured out and an additional filter to narrow the capture of the SUM.

Some how merge these formulas (using my sample db for the purpose of this formula finding mission):

=((MIN(DATE(2023, 06, 30), [End_Date]@row) - MAX(DATE(2022, 07, 01), [Start_Date]@row)) / ([End_Date]@row - [Start_Date]@row)) * [Total_Award]@row

+

{Team db & Tracker Range Prime_Sponsor}, Awarded, {Team db & Tracker Range Prime_Sponsor}, "National Institutes of Health")

When referencing another sheet/grid, we have to use the @cell vs @row, correct?

Thank you so much!

Jamie

Paul Newcome

You would need to leave that formula on the source sheet. Then you would use a basic SUM function on your second sheet.

Jamie Dent

Paul,

Thank you. I'll work with this.

Best,

Jamie