asked on

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

Tj a

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"?

ioecho

ASKER

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.

Danny Child

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",""))

ASKER CERTIFIED SOLUTION

Danny Child

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Danny Child

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

Danny Child

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.