=TODAY() function to fix the date

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..??

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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