Calculating the difference between target end and actual end in working days

JenniferLF

Hi everyone! First post, thanks for taking the time to read. :)

I've got a WBS sheet including three date columns entitled "Target Start," "Target End," and "Actual End." I made a column titled "Schedule Delta," and would like to populate it with a column formula that calculates the difference in days between each row's "Target End" and "Actual End" — the goal being, I could get a sense of how "on schedule" each task was. Ideally, I'd like to then be able to sum up all the deltas and get a sense of how faithfully the project stuck to its deadlines. (If someone has a way better suggestion to get at all that, I'm all ears!)

The way I understand it, I need to moosh together three different formulas for three different circumstances: one where the task was finished early, one where the task is finished late, and one where the task is right on time (all introduced by an IF clause at the beginning to specify that the formula only applies where there is a date in the "Actual End" column). I think I have the three different pieces - they work when I use them independently. But every time I try to put them together (let alone add the preamble "IF" limiting the formula's application to instances where there is a date in the "Actual End" column), I get an error. I've tried many variations/attempts, but I am too much of a newbie. I'd appreciate some feedback from folks with more experience!

Here are the working parts in isolation:
Part One:
=IF([Actual End]@row = [Target End]@row, 0

Part Two:
=IF([Actual End]@row > [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row) - 1, 0)

Part Three:
=IF([Actual End]@row < [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row + 1), 0)

One of my many failed attempts to moosh them together:
=IF((NOT)ISBLANK[Actual End]@row), IF([Actual End]@row = [Target End]@row, 0), IF([Actual End]@row > [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row) - 1, 0), IF([Actual End]@row < [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row + 1), 0)

Thank you so much for your help with my learning journey! :)
Jennifer

Nic Larsen

Does this work for you:

=IF([Actual End]@row = [Target End]@row, 0, IF([Actual End]@row > [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row) - 1, IF([Actual End]@row < [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row + 1), 0)))

bisaacs

Hey @JenniferLF,

For the formula you tried, it looks like you're closing off the IF statements too early, so try something like this:

=IF(ISBLANK([Actual End]@row), 0, IF([Actual End]@row = [Target End]@row, 0, IF([Actual End]@row > [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row) - 1, 0, IF([Actual End]@row < [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row + 1))))

Hope this points you in the right direction!

JenniferLF

Wow!! Huge thanks, @Nic Larsen and @bisaacs ! Your two posts were so kind (and fast?!) and hugely helpful. Thanks to your feedback, I figured it out! Nic was right on with how to combine the statements, and bisaacs explained my error and gave me an idea for how I could do the first IF(ISBLANK) command! Here's what I did in the end, combining both your awesome feedback:

=IF(ISBLANK([Actual End]@row), "", IF([Actual End]@row = [Target End]@row, 0, IF([Actual End]@row > [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row) - 1, IF([Actual End]@row < [Target End]@row, NETWORKDAYS([Target End]@row, [Actual End]@row + 1), 0))))

(I modified the first bit from bisaacs to make the cell simply blank, rather than 0, if there is no entry in "Actual End" column.)

Oodles of appreciation, you two. This is the nicest, most helpful forum I've found on the workternet! :)