Rolling total

Options

Hi, I'm trying to create a total days column based on the following:


start date - end date (ie., 10 days)

1/1/21 - 1/10/21

end date changes to new end date based on extension 1 (i.e., 20 days)

1/1/21 - extended to 1/20/21

extensions can happen at least 4 times (columns are TA EXT 1, TA EXT 2, TA EXT 3 (all are date fields)

Also, a cancellation an impact the total on any of the end dates

so, my total from the above should be 20

if a cancellation happens 1/19/21, the total days number should reflect 19

I think i need a formula that looks first at the start date and end date then if the cancellation field is blank, looks for the extensions - if those are blank, it is end date - start date; if they are not blank, it needs to calculate the days extended... help!

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 02/02/21
    Options

    Hi @DHR Temp Assignment Team 

    Hope you are fine, you can use the following formula and convert it to column formula.

    =IF([TA EXT 4]@row > [End Date]@row, [TA EXT 4]@row - [Start Date]@row, IF([TA EXT 3]@row > [End Date]@row, [TA EXT 3]@row - [Start Date]@row, IF([TA EXT 2]@row > [End Date]@row, [TA EXT 2]@row - [Start Date]@row, IF([TA EXT 1]@row > [End Date]@row, [TA EXT 1]@row - [Start Date]@row, [End Date]@row - [Start Date]@row))))



    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • DHR Temp Assignment Team
    Options

    Hi - thank you so much - this is working for the extensions but if we cancel the TA (at any point during the start or extension period, we need it to recalculate based on the cancellation


    for example TA START DATE 1/1/21 - TA END DATE 1/31/21; TA EXT 1 2/15/21; TA CANX 2/10/21

    original total 31 days, then 31 + 15 (46), then (41)

    also, the start date is counted as well as the end date (1/1/21 - 1/31/21 is actually a total of 31 days)

    Is this a hassle to factor in a cancellation date too? Appreciate your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!