• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 107
  • Last Modified:

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
0
ioecho
Asked:
ioecho
  • 4
1 Solution
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now