Automatic dates in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2140512%22%20slang%3D%22en-US%22%3EAutomatic%20dates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2140512%22%20slang%3D%22en-US%22%3EHi!%3CBR%20%2F%3EI%20have%203%20columns-%20columns%20%231%20contains%20a%20list%20with%20values%20-%20%E2%80%9Cin%20progress%E2%80%9D%20and%20%E2%80%9Cdone%E2%80%9D.%3CBR%20%2F%3EI%20want%20column%20%232%20to%20be%20filled%20with%20the%20date%20columns%20%231%20was%20changed%20to%20%E2%80%9Cin%20progress%E2%80%9D%2C%20and%20column%20%233%20to%20be%20filled%20with%20the%20date%20%231%20was%20changed%20to%20%E2%80%9Cdone%E2%80%9D%2C%20BUT%20I%20want%20the%20date%20in%20column%20%232%20to%20remain%20with%20the%20date%20of%20%E2%80%9Cin%20progress%E2%80%9D.%3CBR%20%2F%3EHow%20can%20I%20do%20that%3F%3CBR%20%2F%3EWhen%20Im%20trying%20now%2C%20I%20get%20the%20date%20in%20%232%2C%20but%20when%20Im%20changing%20%231%20to%20%E2%80%9Cdone%E2%80%9D%20from%20%E2%80%9C%20in%20progress%E2%80%9D%2C%20the%20date%20in%20%232%20deletes%20and%20I%20get%20the%20new%20date%20of%20%E2%80%9Cdone%E2%80%9D%20in%20%233.%3CBR%20%2F%3EI%20want%20them%20both!%3CBR%20%2F%3ESuper%20appreciate%20your%20help%3A)%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2140512%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2141494%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20dates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2141494%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F968693%22%20target%3D%22_blank%22%3E%40MO-38%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20sounds%20like%20you%20are%20tracking%20the%20history%20of%20something%20or%20other.%20It%20could%20be%20helpful%20if%20you%20could%20post%20a%20copy%20of%20the%20full%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20said%2C%20I%20personally%20would%20recommend%20modifying%20the%20way%20you%20track%20the%20history.%3C%2FP%3E%3CUL%3E%3CLI%3ERight%20now%20you%20have%20three%20columns%3A%20a%20single%20column%20that%20names%20the%20stage%20%22In%20Progress%22%20or%20%22Done%22%20and%20two%20columns%20with%20the%20dates%20of%20those%20two%20stages%20respectively%2C%3C%2FLI%3E%3CLI%3EConsider%20having%20just%20two%20columns%2C%20with%20the%20headings%20on%20the%20columns%20being%20%22In%20Progress%22%20and%20%22Done%22%20and%20then%20enter%20the%20dates%20that%20are%20applicable.%3C%2FLI%3E%3CLI%3EOr--and%20this%20would%20be%20more%20of%20a%20way%20to%20keep%20historical%20records%20in%20general--have%20a%20new%20row%20for%20each%20stage%2C%20with%20the%20date%20being%20the%20first%20column%2C%20and%20the%20rest%20of%20the%20row%20being%20stage%20specific%20details.%20This%20will%20enable%20you%20to%20add%20(as%20needed)%20stages%20beyond%20%22In%20Progress%22%20and%20%22Done%22%20to%20include%20such%20things%20as%20%22Revised%22%20%22Defunct%22....whatever.%20I'm%20just%20pointing%20out%20the%20possibilities.%20Depending%20on%20what%20it%20is%20you're%20tracking%2C%20you%20could%20also%20include%20names%20of%20people%20or%20places%2C%20other%20important%20things%20that%20may%20or%20may%20not%20be%20important.%3C%2FLI%3E%3C%2FUL%3E%3CP%3EAre%20you%20amenable%20to%20re-thinking%20the%20process%20along%20lines%20like%20that%3F%20You'd%20be%20creating%20(I%20believe)%20a%20more%20robust%20database%20in%20the%20process%2C%20offering%20a%20lot%20more%20in%20the%20way%20of%20meaning%20and%20value.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2141907%22%20slang%3D%22en-US%22%3ERe%3A%20Automatic%20dates%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2141907%22%20slang%3D%22en-US%22%3EPlease%20share%20some%20sample%20data%20with%20us%2C%2C%2C%20!%3C%2FLINGO-BODY%3E
New Contributor
Hi!
I have 3 columns- columns #1 contains a list with values - “in progress” and “done”.
I want column #2 to be filled with the date columns #1 was changed to “in progress”, and column #3 to be filled with the date #1 was changed to “done”, BUT I want the date in column #2 to remain with the date of “in progress”.
How can I do that?
When Im trying now, I get the date in #2, but when Im changing #1 to “done” from “ in progress”, the date in #2 deletes and I get the new date of “done” in #3.
I want them both!
Super appreciate your help
4 Replies

@MO-38 

So it sounds like you are tracking the history of something or other. It could be helpful if you could post a copy of the full spreadsheet.

 

That said, I personally would recommend modifying the way you track the history.

  • Right now you have three columns: a single column that names the stage "In Progress" or "Done" and two columns with the dates of those two stages respectively,
  • Consider having just two columns, with the headings on the columns being "In Progress" and "Done" and then enter the dates that are applicable.
  • Or--and this would be more of a way to keep historical records in general--have a new row for each stage, with the date being the first column, and the rest of the row being stage specific details. This will enable you to add (as needed) stages beyond "In Progress" and "Done" to include such things as "Revised" "Defunct"....whatever. I'm just pointing out the possibilities. Depending on what it is you're tracking, you could also include names of people or places, other important things that may or may not be important.

Are you amenable to re-thinking the process along lines like that? You'd be creating (I believe) a more robust database in the process, offering a lot more in the way of meaning and value.

Please share some sample data with us,,, !
Hey! Many many thanks for your detailed explanation! I went on adding a separate column for “done” status.
Hey! Many many thanks! I went on adding a separate column for “done” status.