Excel enter date if cell is not blank

[Ctrain]

I am trying to write a formula for when someone enters a date in column C, then todays date will automatically enter into column D.

I am trying to switch from lotus to Microsoft, and need to figure this formula out ASAP. Any help will be greatly appreciated.

Thanks!

 

[SkipVought]

Hi,

What you probably WANT to happen, will not happen with spreadsheet formulas, BECAUSE the formula would return TODAY's date (whatever today is) EVERY TIME the sheet were recalculated.

In addition, there is no spreadsheet function that tests for a date, because, 1) a DATE is just a NUMBER, like TODAY is 38258 and 2) what you see displayed is just a FORMAT. So I could enter the value 1 in the cell and that's a valid value for a date. Happens to be 1/1/1900.

So of the 2 issues, the first can be solved using a Worksheet_Change event macro, that inserts a hard value rather than a formula.

The second issue can also be addressed using VBA Code.

So is this the route that you want to take?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[warpdrve]

Hi -

If all you want is to have Column D populated with the date whenever you enter a date in Column C, just place a formula in Column D that tests for a non-null cell in Column C ... =IF(C2="","",Today()) is one possible solution.

Not sure this is what you really want, but it's a possible workaround.

Regards - Michael

 

[SkipVought]

Michael,

TODAY() is ALWAYS the current date and NOT the date that appeared at the time the cell FIRST changed.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[warpdrve]

Skip,

I guess I misinterpretted the question that was asked earlier ... I knew it couldn't be that simple.

/michael/

 

[Ctrain]

Thank you all for your input. Skip I think the worksheet change macro would be the best. I really need the date to stay as the date it was entered. Or how about this, would I be better off making the user enter the date and give an error message indicating the the entry date can not be prior to todays date? I am not familiar with the worksheet change macro, but I am willing to try whatever you think would be best.

Thanks again for all of your help.

 

[SkipVought]

If you right click the sheet tab, the VB Editor will display the code sheet for that sheet object.

At the TOP of the code window are two dropdowns. The LEFT dropdown lists the OBJECTS, (General) and Worksheet. If you select the Worksheet Object, then the RIGHT dropdown lists the Event Procedures associated with that OBJECT. One of those procedures is the Worksheet_Change Event procedure. This is where your code will go.

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target                   'get out if the change ...
      If .Row <= 2 Then Exit Sub    'is in any row <= the reserve area at the top of my sheet
      If .Column <> 3 Then Exit Sub    'is in any column other than COLUMN C
               
      With .Offset(0, 1)
         .Value = Date
         .NumberFormat = "yyyy/mm/dd"     'make this the date format that you like
      End With
   End With
End Sub

Let me know how this works for you.


Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Ctrain]

Hi Skip sorry for the delay, I was having computer problems, I have added the code you supplied, I am getting a run time error '42' when ever I click in any cell on my worksheet, it says an object is required. Then it starts the debugger, and it highlights the .. If .Row <= 2 Then

I understand the rest of what you are telling me to do, but I am a little confused about that one statement, what is the reserve area at the top of my sheet? I really really appreciate you taking the time to help me out, Thanks again!

 

[SkipVought]

1) Did you copy EVERYTHING in my code example and just paste it into the code sheet? Sounds like you pasted something into the Worksheet_SelectionChange event procedure.

2) If you have som headings and stuff at the top of your sheet, for instance list say that in row 2 you have headings
[tt]
Name Address City Date
[/tt]
If you decided to change City to CityStateZip, you would NOT want the Date heading to be overwritten with the current date, would you?

But then in row 3, you begin entering DATA -- that's where you want the DATE to be inserted.

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Ctrain]

I gotcha, you where correct I had it in the worksheet selection change event. I changed that and it seems to enter the date properly. My only question is the I have other columns that have data in them also and when I go to them I get that same error message, I just select end and it goes on until I get to the column c when I enter there it works fine and enters the date in column D.

Can I use this same code for other columns in the sheet and will it work the same way? I have been informed I have to combine three sheets into one and have an auto fill date field in two other columns.

Thank you so much Skip, you have such a great wealth of knowledge, I really appreciate your time. You have no idea how long I have been trying to get this to work. I am in charge of creating all of our old lotus spreadsheets and databases in Excel and Access it has been a chore. Is there any references that you would recomend that would make this transition any easier?

 

[SkipVought]

My only question is the I have other columns that have data in them also and when I go to them I get that same error message, I just select end and it goes on until I get to the column c

What? I don't understand. You took ALL the code out of the Worksheet_SelectionChange procedure and STILL get errors when you SELECT?

So you want the date to be inserted is anything is entered in any cell in the row?

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target                   'get out if the change ...
      If .Row <= 2 Then Exit Sub    'is in any row <= the reserve area at the top of my sheet
      If .Column = 4 Then Exit Sub
               
      With Cells(.Row, "D")
         .Value = Date
         .NumberFormat = "yyyy/mm/dd"     'make this the date format that you like
      End With
   End With
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Ctrain]

Now that I have really confused you, I found my problem, Spelling. This works great, NOW!

Can I use this same formula for other columns that need a date entered also. They are all date fields, like this one, would I just need to change the column number in the second If statement.

Thank you, thank you, thank you!!!!

 

[SkipVought]

I missunderstood your requirement.

Do you mean that any time you enter data into any column, then a bunch of dates get inserted,

or...
[tt]
enter data in column Date inserted in
C D
H I
M N
[/tt]
something like that -- well EXACTLY what?

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Ctrain]

This should be exactly what I need, and Columns C & D work GREAT!

This is a sheet where three people enter data into, and we need to know when they entered their individual data, we are trying to catch who is not doing their part in a timely fashion.

enter data in column Date inserted in
C D
G H
J K
L M
S T

Does that make more sense?

 

[SkipVought]

Private Sub Worksheet_Change(ByVal Target As Range)
   With Target                   'get out if the change ...
      If .Row <= 2 Then Exit Sub    'is in any row <= the reserve area at the top of my sheet
      Select Case .Column
        Case Cells(1, "D").Column, Cells(1, "H").Column, Cells(1, "K").Column, Cells(1, "M").Column, Cells(1, "T").Column
           Exit Sub
         Case Cells(1, "C").Column, Cells(1, "G").Column, Cells(1, "J").Column, Cells(1, "L").Column, Cells(1, "S").Column
          With .Offset(0, 1)
             .Value = Date
             .NumberFormat = "yyyy/mm/dd"     'make this the date format that you like
          End With
      End Select
   End With
End Sub

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Ctrain]

Thanks for everything and have a super great day!