| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 36134 | 2003-08-01 04:07:00 | Excell spreadsheet formula | alantelfer (2904) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 164375 | 2003-08-01 04:07:00 | I am trying to automate the date entry into a cell conditional to the contents of a different cell. I can use the formula in cell B1 =IF(ISBLANK(A1),"",NOW()) which puts the current date into B1 as I require BUT when I use the spreadsheet on another day the date in B1 will be changed again. I only want the date to be inserted into B1 the first time data is put into A1 then not change, unless you delete the data in A1. |
alantelfer (2904) | ||
| 164376 | 2003-08-01 04:15:00 | You cant use a formula because the nature of these is to amend based on the variables, which in your instance is the current date. Instead, if you want to insert the current date use a shortcut key combo ... Click in the cell you want the date then press the CTRL & ; keys (both keys at once) |
parry (27) | ||
| 164377 | 2003-08-07 11:20:00 | Thank you Parry I asked the same question on another forum and got the answer using VBA - I have included the code if you are interested. you'll need VBA and the worksheet_Change event then Right click on the sheet choose "View code" Select "worksheet" from the left side dropdown select "Change" from the right side dropdown delete the "selection change" template that was auto generated and enter the following in the worksheet change if target.column <> 1 then exit sub target.offset(0,1).value = format(now(),"dd/mm/yy hh:mm:ss") Rgds, Geoff |
alantelfer (2904) | ||
| 164378 | 2003-08-07 12:10:00 | Thanks Alan, theres some very clever people out there. Did you get it from MrExcel? I was aware of target but it means whatever you do in that sheet will be affected, but I see theres a conditional <>1 part there which solves that issue. well done. :-) |
parry (27) | ||
| 164379 | 2003-08-08 06:35:00 | I got the answer from "xlbo" (Geoff's handle) at tek-tips.com on the Microsoft Office forum. He is a wiz with vb. The only problem I have with the code is : If I leave the date format "dd/mm/yy" , the format inserted into the cell is mm/dd/yy so I have changed the line to read format "mm/dd/yy" and it insert the date in the usual NZ format. I have told Geoff of this and he informs me that he does not have this problem, (He's in England) which use the same date format as NZ so this is a mystery. I unfortunatley am NOT a vb GURU so I will live using the code format MM/dd/yy" so I get the format I want. Cheers and thanks for your assistance. Alan |
alantelfer (2904) | ||
| 1 | |||||