Excel Date Function 1

[alan14]

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.

Please be explicit with help details as I am not too familiar with vb programming.

 

[Molby]

Hi Alan,

You could try this. Set the format of a seperate column to be in a date format. Once your formula has returned a match, why don't you copy the contents of all the cells and then PasteSpecial as Values into the new column. That way, the date value in the new column wont change even when the date moves on a day.

Hope that helps,

Regards,

Ian

 

[alan14]

What I am trying to acheive is when reference Cell (A1,A2,A3 etc)has Data inserted, the date the data was inserted is recorded automatically (B1,B2,B3 etc)

 

[xlbo]

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(),&quot;dd/mm/yy hh:mm:ss&quot

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[alan14]

Thank you xlbo (Geoff) your suggestion works a treat. I have managed to work out the changes to relate it to the necessary cells in my spreadsheet.
&quot;I tested your code 7 August 2003&quot;
I do however have a question, the line in your code which specifies the date format (dd/mm/yy), if I leave as is the date which is entered into the spreadsheet is 8/7/03 (8 July 03) but if I change the code to read format mm/dd/yy the date entered is correct.
The date format setting in Regional Settings on my computer is set to display dd/mm/yy (this is the ususl format we use in New Zealand). Any suggestions ??

Cheers Alan

 

[xlbo]

Alan - not sure about that as it works fine for me in the uk (we also use dd/mm/yy) and it comes out as 08/07/03 - I presume that the issue is that you were expecting the &quot;0&quot;s to appear ???
Is this a real problem ???? as long as it is still read as a date, you should be ok

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[alan14]

Geoff - It is not a problem as I will use your code with the date format as mm/dd/yy to get the correct date inserted into the spreadsheet. You stated in your reply thet you got the correct date when you tried BUT you typed 08/07/03!!! You should have got 07/08/03 if you tried it yesterday, Which is the problem I stated

Cheers Alan

 

[xlbo]

my bad - typo I think - comes out as 08/08/03 today ;-) - not a good day to figure out whether it's dd/mm or mm/dd

Problem is VBA luuuurrves mm/dd/yy and it can be a real pain to make it change its mind - this should do it:

If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Value = CDec(Now())
Target.Offset(0, 1).NumberFormat = &quot;dd/mm/yy hh:mm&quot;

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[alan14]

Hi Geoff
Tried your new code but returns
Run-time error '1004':
Application-defined or object-defined error

I copied and pasted your code to ensure I didn't type it wrong

As I said earlier I can use your code revesing the date format, unless your like me and hate it when things don't do what they are supposed to do.

I changed the date format of the spreadsheet cell to 07 Aug 03 when I was testing to avoid misreading what was inserted into the destination cell.

Cheers
Alan

 

[alan14]

Hi Geoff
I was playing around with your code

If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Value = CDec(Now())
Target.Offset(0, 1).NumberFormat = &quot;dd/mm/yy hh:mm&quot;

and found if I deleted 'CDec'it gives me the correct format
New Code: (I also removed time)

If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Value = (Now())
Target.Offset(0, 1).NumberFormat = &quot;dd/mm/yy&quot;

Thank you VERY much for your assistance

 

[xlbo]

No probs - glad you got it sorted

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244