Automate dates

AHANSON

Hi, I'm trying to automate dates being filled in based on a certain schedule:

Basically C1D1 is day 1 and C1D2 is day 2 and so forth...

I'm envisioning a formula that reads "if [Visit #]@row contains D2 (or whatever day), then add 1 day or 2 days, etc. to day 1.

I have part of the formula:

=IF(CONTAINS("D2", [Visit #]@row), Planned2 + 1)

But I'd like to optimize the formula so that I can make a column formula and it will take the ((day x-1) + day 1). I think I need a LEFT function somewhere.

Thanks for your help!

Paul Newcome

My suggestion would be a parent row that houses the Planned date you want to lock in. Then you can reference PARENT(Planned@row).

L_123

=value(right([visit #]@row,len([visit #]@row) -3))+planned2

AHANSON

Thank you for the formula, you definitely put me on the right track. I modified it to get the correct dates:

=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + Planned2 - 1

While this formula works, I needed the "Planned2" to be constant, just like if you did $C$1 in excel to keep the cell in a formula constant.

This formula works perfectly.

=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + $Planned$2 - 1

I'd like to optimize this even further if possible:

You'll notice that there are several "CxD1" in this scenario. I need the CxD2, D3, D8, D15 to build on the respective CxD1. Any suggestions on how to modify the above equation?

Thanks again for your help!

AHANSON

I tried this but it didn't work:

=VALUE(RIGHT([Visit #]@row, LEN([Visit #]@row) - 3)) + IF(CONTAINS("D1", [Visit #]@row), [Visit #]@row - 1)

Paul Newcome

My suggestion would be a parent row that houses the Planned date you want to lock in. Then you can reference PARENT(Planned@row).

AHANSON

Thank you so much! This was really helpful!

Paul Newcome

Happy to help. 👍️