MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Datefunctions


Posted by Brynjar Hallmannsson on May 26, 2001 5:16 AM

I am using an IF statement to fill in a date in a cell when a name is entert in another cell, IF(C60>" ",TODAY()," ").
The problem is that it alters the date everytime I open the workbook. I´m sure it is a very simple solution, but I´m stuck. Please help


Posted by Dave Hawley on May 26, 2001 10:03 PM


Hi Brynjar

If your name could be Text or Numeric:
IF(C60<>"",TODAY(),"")


If it is only Text then:
=IF(AND(C60<>"",NOT(ISNUMBER(C2))),TODAY(),"")

If only a number:
=IF(AND(C2<>"",ISNUMBER(C2)),TODAY(),"")


Dave


OzGrid Business Applications

Posted by Aladin Akyurek on May 28, 2001 5:58 AM

Brynjar

If it is your intention that the date value never change once entered, then you have a problem there. You should ask for a task specific VBA code.

Aladin

Posted by Brynjar Hallmannsson on May 28, 2001 10:04 AM

Aladin tipped me that I need a task specific VBA code, can anyone help me, please !-}

Brynjar H

Posted by Damon Ostrander on May 29, 2001 9:11 AM

Hi Brynjar,

Aladin notified me of your problem and need for a VBA solution. Yes, this problem does need a VBA solution, and this solution is fairly simple. You didn't mention where you want the date written, but suppose for example you want the date entered in the cell just to the right of C60 when a value is entered or changed in C60, BUT NOT WHEN THE SHEET IS CALCULATED, as happens with the function you described. Simply insert the following code into the worksheet's event code area:

Private Sub Worksheet_change(ByVal Target As Range)

If Not Intersect(Target, Range("C60")) Is Nothing Then
Range("D60") = Date 'Today's date
End If

End Sub


This procedure will be triggered whenever anything changes on the worksheet. The code I wrote simply checks to see if the cell that has changed is C60, and if so puts the date into D60.

If you don't know how to install a worksheet event code macro,
it's really quite easy. Just follow these steps:

1) Go to the Visual Basic Editor (VBE). Do this from Tools ->
Macro -> Visual Basic Editor.

2) In the VBE Project pane (upper left corner of the VBE) you
will see a tree diagram of all the workbook and sheet
objects open in Excel. Find the worksheet you want to add
the event code to in the tree and double click on its
icon. An empty worksheet code window will pop up.

3) There will be two dropdown controls at the top of this code
window, labeled "(General)" and "(Declarations)". In the
General dropdown select "Worksheet". In the code pane a
Worksheet_SelectionChange template will be created. If this
is not the worksheet event that you want, select the event
you do want in the Declarations dropdown.

3) Paste the code you want to run when this event occurs into
this event routine template. The event is now active. Note
that some of the event routines have arguments that provide
useful information. For example, Worksheet_Change
provides an argument named Target that contains the range
that has been selected on this worksheet.

Happy computing.

Damon

Posted by Brynjar Hallmannsson on May 30, 2001 1:00 AM

Re: Exactly what I needed =|:-)>----

notified me of your problem and need for a VBA solution. Yes, this problem does need a VBA solution, and this solution is fairly simple. You didn't mention where you want the date written, but suppose for example you want the date entered in the cell just to the right of C60 when a value is entered or changed in C60, BUT NOT WHEN THE SHEET IS CALCULATED, as happens with the function you described. Simply insert the following code into the worksheet's event code area: