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
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
Microsoft Excel
Last Comment
Danny Child
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.
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.
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),"Complete
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),"Complete
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
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
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.
Do-able, just a bit clumsy.
So you want Start date in both Column B and C if the status = "InProgress"?