Find Text & Convert to Date Values

[mycon73]

Hi

Creating a project tracker with a 3-Week Look Ahead

As you will see in attached example, we are tracking what we call CPs and its Turn Over (TO) dates

Currently we are tracking PLANNED Turn Over dates but not ACTUAL dates


Need assistance to modify

'=IF(D20="","",IF(D20="X","X",DATEVALUE(RIGHT(D20,LEN(D20)-2))))


See example
-- This is currently showing PLANNED CPs per day or week
-- Want to show both PLANNED & TURNED OVER (TO) dates

For example - With Date, TO 9/8, it will show "TO 9/8" in its applicable cell

[protonLeah]

Column F?

Please Login or Register  to view this content.

[mycon73]

HI protonLeah

Thanks for giving feedback!

Although your function works well, need the following:

In my example, if column D has a date or DOES NOT have TO (for Turn Over), then it's considered a PLANNED CP (mini-project IDs)

With the function I'm using, it's resulting with #VALUE!

I want this result to be blank or not give a date

Primary objective is to distinguish PLANNED vs. ACTUAL completion dates - COMPLETED CPs will have "TO MM/DD" in my 3-Week Look Ahead table
-- I already have functions to extract inputs from column F to get into table

Thanks

[JeteMc]

I feel that this will produce the output you want in F2 and down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[mycon73]

Hi JeteMC & Others

Thanks for giving feedback!

Your function works well for "TO" conversions to date!!
-- These dates represents ACTUAL TURN OVER date(s)

Existing dates WITHOUT "TO" represents PLANNED dates
-- For these findings, results can be "" or blank

Request assistance to modify

Thanks

[mycon73]

JeteMC

I modified your function to:

=IF(D20="","",IF(D20="x","X",IF(ISNUMBER(D20),"",DATEVALUE(RIGHT(D20,LEN(D20)-2)))))

Now, it appears to giving desired results!!!

Let me test it out a bit more to confirm

Thanks for the assistance

[mycon73]

JeteMC, protonLeah & Others

Thanks to your assistance - Modified to the following to get desired results!

=IF(D20="","",IF(D20="x","X",IF(ISNUMBER(D20),"",DATEVALUE(RIGHT(D20,LEN(D20)-2)))))

Thanks

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.