I want to fix the date in a cell with a formula whenever other cell is
populated.
e.g when cell B1 is populated, cell A1 should show the date of that day
and that should not change later on.
I have seen some posts with the same question but with no answer.
any guru on this..??


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=561042

In cell A1 place this formula:

=IF(ISBLANK(E1),"",DATE(YEAR(TODAY()),MONTH(TODAY( )),DAY(TODAY())))

If the result displays as an integer, format the cell using the date format
of choice.
--
Kevin Backmann


"starguy" wrote:


I want to fix the date in a cell with a formula whenever other cell is
populated.
e.g when cell B1 is populated, cell A1 should show the date of that day
and that should not change later on.
I have seen some posts with the same question but with no answer.
any guru on this..??


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=561042

Starguy,

You need to use a worksheet change event to do that: for example, for
any cell in column B, the date when the entry is made or changed is stored in column A
using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("B:B"))
Cells(myCell.Row, 1).Value = Now
Cells(myCell.Row, 1).NumberFormat = "mm/dd/yy hh:mm:ss"
Next myCell
Application.EnableEvents = True
End Sub

Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP


"starguy" wrote in message
...

I want to fix the date in a cell with a formula whenever other cell is
populated.
e.g when cell B1 is populated, cell A1 should show the date of that day
and that should not change later on.
I have seen some posts with the same question but with no answer.
any guru on this..??


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=561042

Kevin, will not the date change in your case??

Kevin B skrev:

In cell A1 place this formula:

=IF(ISBLANK(E1),"",DATE(YEAR(TODAY()),MONTH(TODAY( )),DAY(TODAY())))

If the result displays as an integer, format the cell using the date format
of choice.
--
Kevin Backmann


"starguy" wrote:


I want to fix the date in a cell with a formula whenever other cell is
populated.
e.g when cell B1 is populated, cell A1 should show the date of that day
and that should not change later on.
I have seen some posts with the same question but with no answer.
any guru on this..??


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=561042

date is changed, any other to solve this problem without VBA (if
possible)


=IF(ISBLANK(E1),"",DATE(YEAR(TODAY()),MONTH(TODAY ()),DAY(TODAY())))

If the result displays as an integer, format the cell using the date
format
of choice.
--
Kevin Backmann


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=561042