Constant date in Excel 2K7

**adivishnu** · 02-18-2011, 05:28 AM

Hi there people

i have prblem with date function in excel 2oo7. My question is, i have a sheet with lot of data and particulary i am looking at two columns (Status column & Date column). the status column will be having data values like "IC", "INS" , etc. next to it the date column will be there. problem is i change the status from "INS" to "IC" on february, 18,2011, in the date column i want today's date to be displayed automatically and should remain constant forever.
Status Date
INS
INS
INS
INS
INS
INS

Any help will be appreciated. Thanks in advance.

Regards

Adi

**Colin Legg** · 02-18-2011, 05:38 AM

Hi,

You basically have two choices.

(1) You can keep the date formulae in the Date column, but when you type IC into the Status column you will have to remember to copy/pastespecial values the corresponding Date formula so that the date doesn't continue to change.
(2) Use VBA to automatically add the date into the Date column. The approach would be to use the Worksheet_Change() event handler and to put the date in the cell if the corresponding cell in the Status column contains "IC".

If you need help with option (2) then please let us know which columns are the Status and Date columns.

**adivishnu** · 02-18-2011, 05:47 AM

Hi Colin

Thanks for the fast reply. i really appreciate it. i am looking an automated value in the date column. as i am really naive in VB, please help me with detailed steps to create the program. would like to go with option 2. thanks in advance.

Regards
Adi

**Colin Legg** · 02-18-2011, 05:53 AM

Sure, no problem.

Which columns (the column letters) are the Status and Dates columns?

**adivishnu** · 02-18-2011, 05:58 AM

Thanks, Status is the M Column and date is the N column

**Colin Legg** · 02-18-2011, 06:35 AM

Hi,

Define the following names in your workbook (under name manager) but change Sheet1 to the name of your worksheet.

Dates =Sheet1!$N:$N
Status =Sheet1!$M:$M

Then, right click on the sheet's tab (at the bottom of the grid) > view code. This will open up the Visual Basic Editor. Paste in the below code and you're done. If "IC" is in the Status column then the code will add the date to the Dates column; if anything else has been put in the Status column then the code will clear the Dates column.

Please Login or Register  to view this content.

**adivishnu** · 02-18-2011, 06:48 AM

Colin thank you very much for the help. I didn't understand one thing where exactly, i have to define the names in the workbook. Name Manager??????. Can you please more specific.

Thanks in advance.

Regards

Adi

**Colin Legg** · 02-18-2011, 06:51 AM

Hi,

You're using Excel 2007 so on the Ribbon go to the Formulas tab and then either click on Name manager or "Name a Range" (this might also be called "Define Name" - I only have XL 2003 at work so I can't check) to get the named range set up. Have a read through this for further information - names are a very, very useful tool.

http://office.microsoft.com/en-us/ex...010147120.aspx