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

Options
Jamie Dent
Jamie Dent ✭✭✭
edited 11/07/22 in Formulas and Functions

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

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Jamie Dent ✭✭✭
    edited 11/08/22
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Jamie Dent ✭✭✭
    Options

    Paul,


    Thank you. I'll work with this.


    Best,

    Jamie

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!