Capturing Date and Time Stamp

Column A  = Status = Either 'Completed', or "InProgress"
Column B =IF(A2="InProgress",NOW(),"-")
Column C  =IF(A2="Complete",NOW(),"-")

When I change the status in column A to "InProgress," I need to capture the "Start Date" in column C. When I subsequently change the status for that cell to "Completed" I need to capture th "End Date" in column C while leaving the start dates as they are. Does anyone know how to write a formula that will do just that?

Status                    Date Started               Date Completed
Completed                    -                      4/27/2015
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                     -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
InProgress      4/27/2015                      -
Completed      -                                     4/27/2015
Completed      -                                     4/27/2015
Completed      -                                      4/27/2015
ioechoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
You said, "When I change the status in column A to "InProgress," I need to capture the "Start Date" in column C"

So you want Start date in both Column B and C if the status = "InProgress"?
0
ioechoAuthor Commented:
Sorry,

This is what I meant...

When I change the status in column A to "InProgress," I need to capture the "Start Date" in column B. When I subsequently change the status for that cell to "Completed" I need to capture the "End Date" in column C while leaving the start dates in Column B as they are. Does anyone know how to write a formula that will do just that?


Each line corresponds to a particular test. When I begin the test I will change the status to "InProgress. Column B should record the date when this value is change. When I finish a test I will change the status to "Completed." Column B should not change again, but Column C should record the "Completed" date.
0
Danny ChildIT ManagerCommented:
it may be easier to reverse this problem.  The trouble with using the NOW() function is that if your sheet recalculates, all dates will change to the *current* date.

The keyboard shortcut CTRL + ; inserts the current date

If you were to use these keystrokes in cols B and C to enter the relevant dates, you could use this formula in Col A to set the corresponding InProgress or Completed statements
=IF(ISNUMBER(C2),"Completed",IF(ISNUMBER(B2),"InProgress",""))
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Danny ChildIT ManagerCommented:
it has 1 IF statement nested inside the other.  The inner one returns either InProgress or <blank>, depending on whether the Start Date has been entered.
The outer one returns either Completed, or the results of the inner one as above, depending on whether the Date Completed has been entered.

(on a related note, CTRL + : inserts the current time - note, on a UK keyboard, this means that I need to hold CTRL and SHIFT, and then hit the ; button)

Your keyboard layout may be different, but it will have the same effect even if you have to hit different keys to get these characters.
ee-Start-Completed-date-status.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Danny ChildIT ManagerCommented:
by the way, I see you've submitted a Request for Attention.
here's the site's advice on what this function is for
http://support.experts-exchange.com/customer/portal/articles/539391-the-request-attention-system
0
Danny ChildIT ManagerCommented:
if you do want to use the NOW function, you'll need to do Copy/Paste Special.. Values afterwards to hard-code those dates in, and you'll need to do this every time a status changes.  
Do-able, just a bit clumsy.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.