Rolling total

DHR Temp Assignment Team

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!

Bassam Khalil

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))))

DHR Temp Assignment Team

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!