Automatically dated cells

[SwimmingNathan]

Hiya,

I've posted about this problem before, without much luck finding a simple (or working) answer.

I want a cell to display the current date when another cell has something entered into it.

Example: I enter @ into A1, I want B1 to display the date without me having to either click on it and enter =TODAY or enter the date manually.

There must be another formula that can be used with an IF function to say whether or not the cell is empty.

I feel that if I could get a Y/N response on whether there is anything in A1 then combining the IF and TODAY will be a doddle.

Excel 2013

Please don't mention change tracking.

Any help is greatly appreciated.

Cheers,

Swimming

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. If you enter anything in Column A, the current date will automatically be placed in column B.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Range("B" & Target.Row) = Date
End Sub

 

[mumps]

If you want to use a formula, format column B as 'Date'. Enter this formula in B1: =IF(A1<>"",TODAY(),""). Enter some data in A1 and the date will appear in B1.

 

[SwimmingNathan]

If you want to use a formula, format column B as 'Date'. Enter this formula in B1: =IF(A1<>"",TODAY(),""). Enter some data in A1 and the date will appear in B1.

http://s24.postimg.org/exyhfnu1x/excel_test.png

Is what I get when entering the formula as written.

I'm about to try the macro.

 

[mumps]

You have to format column C as a 'Date'.

 

[SwimmingNathan]

You have to format column C as a 'Date'.

Oh yep, forgot to do that, working perfectly, thanks for your help it's greatly appreciated.

If I may ask, how does the formula actually work? What function do the <> & "" work in the logical test of the IF statement?

 

[mumps]

Glad I could help. =IF(A1<>"",TODAY(),"") The formula: (if A1 is not equal to a blank cell, enter today's date, otherwise leave it blank.) I hope this helps.

 

[SwimmingNathan]

Glad I could help. =IF(A1<>"",TODAY(),"") The formula: (if A1 is not equal to a blank cell, enter today's date, otherwise leave it blank.) I hope this helps.

Ahh I see I didn't know you could even do 'not equal to'. Amazing, thanks a bunch!

 

[mumps]

My pleasure.

 

[SwimmingNathan]

Sorry to re-awaken this thread, however I have a slight dilemma, or rather a requirement I should have stated first time around.

This doesn't happen to all the cells in a column, but when opening a spreadsheet some of the dates update to the current date.

I started using the Macro above as it seemed to happen less frequently than when I was using the formula, however both methods now update the date column undesirable.

Just to be clear on what I'm after, I want to enter something into A1 and the current date to be entered in B2, I don't want the date to update when A1 is edited. In fact it would be good if that date was set in stone, if you will, the only way to get rid of it being to enter that cell and manually delete.

Any and all help appreciated with immense gusto as usual.

Thanks,

Swimming