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
