help with dates in an IF statement

DHR Temp Assignment Team

Hi Wonderful Formula Wizards, I am using the below formula to calculate the number of days an employee is working in a temp assignment - I just found out these employees' assignments can be paused (due to injury or vacation, etc.) and restarted. This means that I need to add the following columns and include the pause and restart dates in my formula below. I will have a TA PAUSE DATE, TA RESTART DATE, TA EXT PAUSE DATE, TA EXT 1 RESTART DATE, etc. (for every EXT (up to 4)

If employee begins an assignment 1/1/21 and is schedule to end on 6/30/21 the total days would be 181; if the TA is paused 1/31/21, total days should be 31 until the assignment is restarted; if the TA is restarted on 2/15/21, the total days needs to reflect 31+15 = 46 + # days until the TA END DATE 6/30/21;

I'm not quite sure how to include these variables in the below; obviously if any of the TA pause dates are blank, the formula should just function as below; I created help columns for total TA Pause days.

=IF([TA CANX]@row <> "", [TA CANX]@row - [TA START DATE]@row, IF([TA EXT 4]@row > [TA END DATE]@row, [TA EXT 4]@row - [TA START DATE]@row, IF([TA EXT 3]@row > [TA END DATE]@row, [TA EXT 3]@row - [TA START DATE]@row, IF([TA EXT 2]@row > [TA END DATE]@row, [TA EXT 2]@row - [TA START DATE]@row, IF([TA EXT 1 PAUSE DATE]@row > [TA END DATE]@row, [TA EXT 1 PAUSE DATE]@row - [TA START DATE]@row, [TA END DATE]@row - [TA START DATE]@row))))) + 1

Paul Newcome

Does this look about right?

There are the initial start and end dates. Then we build in a couple of pause/resume dates, and then we also have a couple of extension dates which replace the original end date. Except you have 4 sets instead of two.

What you want to do is take the end date (or extension date) and figure out how many days that is from the start and then subtract the number of days in the pause columns. If it is paused with no resume, then the end date is the pause date.

Is all of that correct?

Paul Newcome

I think I may have a few ideas. Are you able to provide a screenshot with a few manually entered sample entries that shows what you are working with and what your expected outcomes would be?

DHR Temp Assignment Team

Thank you! I was hoping you would see this :)

I just sent you a few screen shots - I didn't create all the columns yet because I'm not sure if I can achieve what I am being asked to do -

Paul Newcome

I don't see any screenshots. Are you able to repost them?

Even if you haven't created ALL of the columns yet, if you could just throw a few in there as an example, we can at least get a base started.

I do feel pretty confident thought that we can accomplish what you are looking for with a little bit of creativity.

DHR Temp Assignment Team

for some reason, ss is not allowing me to upload any images, i keep trying to no avail.

i have the following columns

TA START TA END TA Total Days TA EXT 1 TA EXT 2, 3, 4 (these are date fields and would be a new TA END date) I just created a TA Pause Date and a TA Restart Date as well as the same colunms for TA EXT 1 - 4 (any TA EXT can be paused and restarted as well); Each group TA START TA END or TA EXT 1 has a TA Total as well (TA END - TA START) TA EXT 1 TTL (TA EXT 1 end date - TA START DATE)

Is that helpful ????

Paul Newcome

Does this look about right?

There are the initial start and end dates. Then we build in a couple of pause/resume dates, and then we also have a couple of extension dates which replace the original end date. Except you have 4 sets instead of two.

What you want to do is take the end date (or extension date) and figure out how many days that is from the start and then subtract the number of days in the pause columns. If it is paused with no resume, then the end date is the pause date.

Is all of that correct?

DHR Temp Assignment Team

Hi Paul, yes at this time we allow 4 extension opportunities only (that may change but we did an analysis and we did not find in the past 3 years any situations with 5 extensions).

So after the first start date, you should be able to pause and restart (I think I neglected to create a column to show that), if you extend, you should be able to pause and restart during any extension period, if you start you should also be able to cancel at any point in either during the initial assignment or during an extension to the assignment (this is working with the current formula).

So, here's a scenario: Joe started a TA on 1/1/21 through 1/31/21; but it was extended to 3/31/21, but Joe was injured on 2/5; his assignment needs to be paused on 2/5; Joe returns to the assignment 3/5/21 so we need to restart the EXT 1 on 3/5; but oops Joe ended up with relapse and the program decided to just cancel the assignment on 3/9/21. Joe's total days in this assignment should calc like this - 1st start to end = 31 days; EXT 1start to end = 31+28+31 (90 total) days; due to injury the total days reflects 1/1/21 - 2/5/21 (36 days); after eturn on 3/5 total days should be 36 + 27 = 63 total days, 3/5/21 - 3/31/21 (original end of extension); cancellation on 3/9 will change the total days from 63 to 36+5 (41 total days for the assignment from beginning to end). Again this would need to be repeated up to 5 times (one original, 4 potential extensions).

Thank you again for helping me with this -- I owe you a beer!

Paul Newcome

OK. I will try to break it down so that you can add additional extensions and pauses if needed just in case. I definitely prefer to build in some growth flexibility when I can.

So let's start with looking across your columns and establishing an "Actual End Date".

If there is 1 or more extensions, then the furthest out extension date is our new end date.

=MAX([End Date]@row, [1st Extension Date]@row, [2nd Extension Date]@row, [3rd Extension Date]@row, [4th Extension Date]@row)

But now we need to also evaluate the pause dates that do not have a resume date. For that one we are going to have to use a nested IF.

=IF(AND([4th Pause]@row <> "", [4th Resume]@row = ""), [4th Pause]@row, IF(AND([3rd Pause]@row <> "", [3rd Resume]@row = ""), [3rd Pause]@row, IF(AND([2nd Pause]@row <> "", [2nd Resume]@row = ""), [2nd Pause]@row, IF(AND([1st Pause]@row <> "", [1st Resume]@row = ""), [1st Pause]@row))))

Now we combine the two to say that if there are any pauses without a resume, use that, otherwise (if there are no pauses without a resume) use the MAX.

=IF(AND([4th Pause]@row <> "", [4th Resume]@row = ""), [4th Pause]@row, IF(AND([3rd Pause]@row <> "", [3rd Resume]@row = ""), [3rd Pause]@row, IF(AND([2nd Pause]@row <> "", [2nd Resume]@row = ""), [2nd Pause]@row, IF(AND([1st Pause]@row <> "", [1st Resume]@row = ""), [1st Pause]@row, MAX([End Date]@row, [1st Extension Date]@row, [2nd Extension Date]@row, [3rd Extension Date]@row, [4th Extension Date]@row)))))

Now we have our "Actual End Date" that we can subtract our [Start Date] from to get our total days.

=[Actual End Date]@row - [Start Date]@row

If you want to avoid having a helper column for [Actual End Date], then we can take our nested IF and subtract the start date from that directly.

=IF(AND([4th Pause]@row <> "", [4th Resume]@row = ""), [4th Pause]@row, IF(AND([3rd Pause]@row <> "", [3rd Resume]@row = ""), [3rd Pause]@row, IF(AND([2nd Pause]@row <> "", [2nd Resume]@row = ""), [2nd Pause]@row, IF(AND([1st Pause]@row <> "", [1st Resume]@row = ""), [1st Pause]@row, MAX([End Date]@row, [1st Extension Date]@row, [2nd Extension Date]@row, [3rd Extension Date]@row, [4th Extension Date]@row))))) - [Start Date]@row

Now we need to figure out how many days were paused. That is going to be a series of IF statements "added" together to say that if the pause and resume are filled in, figure out the difference. Then add those IFs together.

=IF(AND([1st Pause]@row <> "", [1st Resume]@row <> ""), [1st Resume]@row - [1st Pause]@row)

Now we duplicate this for each of the other pause/resume sets and add them together.

=IF(AND([1st Pause]@row <> "", [1st Resume]@row <> ""), [1st Resume]@row - [1st Pause]@row) + IF(AND([2nd Pause]@row <> "", [2nd Resume]@row <> ""), [2nd Resume]@row - [2nd Pause]@row) + IF(AND([3rd Pause]@row <> "", [3rd Resume]@row <> ""), [3rd Resume]@row - [3rd Pause]@row) + IF(AND([4th Pause]@row <> "", [4th Resume]@row <> ""), [4th Resume]@row - [4th Pause]@row)

Now we have our [Total Pause] that we can subtract from the Total Days.

=[Actual End Date]@row - [Start Date]@row - [Total Pause]@row

=IF(AND([4th Pause]@row <> "", [4th Resume]@row = ""), [4th Pause]@row, IF(AND([3rd Pause]@row <> "", [3rd Resume]@row = ""), [3rd Pause]@row, IF(AND([2nd Pause]@row <> "", [2nd Resume]@row = ""), [2nd Pause]@row, IF(AND([1st Pause]@row <> "", [1st Resume]@row = ""), [1st Pause]@row, MAX([End Date]@row, [1st Extension Date]@row, [2nd Extension Date]@row, [3rd Extension Date]@row, [4th Extension Date]@row))))) - [Start Date]@row - [Total Pause]@row

=IF(AND([4th Pause]@row <> "", [4th Resume]@row = ""), [4th Pause]@row, IF(AND([3rd Pause]@row <> "", [3rd Resume]@row = ""), [3rd Pause]@row, IF(AND([2nd Pause]@row <> "", [2nd Resume]@row = ""), [2nd Pause]@row, IF(AND([1st Pause]@row <> "", [1st Resume]@row = ""), [1st Pause]@row, MAX([End Date]@row, [1st Extension Date]@row, [2nd Extension Date]@row, [3rd Extension Date]@row, [4th Extension Date]@row))))) - [Start Date]@row - (IF(AND([1st Pause]@row <> "", [1st Resume]@row <> ""), [1st Resume]@row - [1st Pause]@row) + IF(AND([2nd Pause]@row <> "", [2nd Resume]@row <> ""), [2nd Resume]@row - [2nd Pause]@row) + IF(AND([3rd Pause]@row <> "", [3rd Resume]@row <> ""), [3rd Resume]@row - [3rd Pause]@row) + IF(AND([4th Pause]@row <> "", [4th Resume]@row <> ""), [4th Resume]@row - [4th Pause]@row))

And there you have it. You can break it down into various helper columns or leave it as one big formula.

Let me know how it works for you.