date stamp
 

Shawn,

This came up recentlyu, and the best soluition IOMO was this one from Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t have to be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather than
the developer, who would need to leave __NOW__ undefined or initialized to
#N/A). Then the name __NOW__ could be used anywhere in any formula in the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date is fine
as
the initial input to a cell, but I want to datestamp the dates on the
master
sold sheet so they do not update. Actually, I want to do this to the
whole
column so as dates get entered periodically, they do not change from the
inital value.

What do I need to do and thanks.

date stamp
 

Shawn,

Problem with this function is that it can recalculate, which would mean that
your date is updated. You need one that doesn't.

To answer the previous question here's how to enter the code.

To enter it, on the sheet tab, right-click and select View Code. In the VB
IDE in the project pane on the left for the workbook you are working on,
select ThisWorkbook from the list of Microsoft Excel objects (it has an
Excel
icon beside it) by double-clicking to open the code module. Enter the code.

You then use it in a formula like this

=IF(A2<0, __NOW__)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to advanced
excel
techniques and what you just said has gone a bit over my head. I was
hoping
for a cut and paste job but I might as well ask you to break it down for
me.
How exactly does this work? Do I just open the vb editor type this in?
I
have a basic understanding of names, instead of A1, I could refer to a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t have
to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather
than
the developer, who would need to leave __NOW__ undefined or
initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date is
fine
as
the initial input to a cell, but I want to datestamp the dates on
the
master
sold sheet so they do not update. Actually, I want to do this to
the
whole
column so as dates get entered periodically, they do not change from
the
inital value.

What do I need to do and thanks.

date stamp
 

How about fixing your clock so you are not posting from the future.

--
Regards,
Tom Ogilvy

shawn modersohn wrote in message
...
Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to advanced
excel
techniques and what you just said has gone a bit over my head. I was
hoping
for a cut and paste job but I might as well ask you to break it down for
me.
How exactly does this work? Do I just open the vb editor type this in?
I
have a basic understanding of names, instead of A1, I could refer to a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t have
to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather
than
the developer, who would need to leave __NOW__ undefined or
initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date is
fine
as
the initial input to a cell, but I want to datestamp the dates on
the
master
sold sheet so they do not update. Actually, I want to do this to
the
whole
column so as dates get entered periodically, they do not change from
the
inital value.

What do I need to do and thanks.

date stamp
 

Shawn - we've all done that before.. I know I have ;)


"shawn modersohn" wrote in message
...
Thanks for all the help, I really appreciate it. The clock is a result of
me trying to see what gets updated in the workbook, I just forget to set
it
back before posting.


"Tom Ogilvy" wrote in message
...
How about fixing your clock so you are not posting from the future.

--
Regards,
Tom Ogilvy

shawn modersohn wrote in message
...
Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to
advanced
excel
techniques and what you just said has gone a bit over my head. I
was
hoping
for a cut and paste job but I might as well ask you to break it down
for
me.
How exactly does this work? Do I just open the vb editor type this
in?
I
have a basic understanding of names, instead of A1, I could refer to
a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your
solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one
from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t
have
to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with
the
date/time value when the workbook was first opened by a user
(rather
than
the developer, who would need to leave __NOW__ undefined or
initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula
in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's
date
is
fine
as
the initial input to a cell, but I want to datestamp the dates
on
the
master
sold sheet so they do not update. Actually, I want to do this
to
the
whole
column so as dates get entered periodically, they do not change
from
the
inital value.

What do I need to do and thanks.

date stamp
 

You were absolutely right. Thanks for the help, I got it figured out. If I
pay attention here I just might learn something.


"Bob Phillips" wrote in message
...
Shawn,

Problem with this function is that it can recalculate, which would mean
that
your date is updated. You need one that doesn't.

To answer the previous question here's how to enter the code.

To enter it, on the sheet tab, right-click and select View Code. In the VB
IDE in the project pane on the left for the workbook you are working on,
select ThisWorkbook from the list of Microsoft Excel objects (it has an
Excel
icon beside it) by double-clicking to open the code module. Enter the
code.

You then use it in a formula like this

=IF(A2<0, __NOW__)


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to advanced
excel
techniques and what you just said has gone a bit over my head. I was
hoping
for a cut and paste job but I might as well ask you to break it down
for
me.
How exactly does this work? Do I just open the vb editor type this
in?
I
have a basic understanding of names, instead of A1, I could refer to a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your
solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one
from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t
have
to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather
than
the developer, who would need to leave __NOW__ undefined or
initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula
in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date
is
fine
as
the initial input to a cell, but I want to datestamp the dates on
the
master
sold sheet so they do not update. Actually, I want to do this to
the
whole
column so as dates get entered periodically, they do not change
from
the
inital value.

What do I need to do and thanks.

date stamp
 

I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14) for
a date column. d14 refers to today's date. The d14 today's date is fine as
the initial input to a cell, but I want to datestamp the dates on the master
sold sheet so they do not update. Actually, I want to do this to the whole
column so as dates get entered periodically, they do not change from the
inital value.

What do I need to do and thanks.

date stamp
 

Perhaps I should have explained that I am just progressing to advanced excel
techniques and what you just said has gone a bit over my head. I was hoping
for a cut and paste job but I might as well ask you to break it down for me.
How exactly does this work? Do I just open the vb editor type this in? I
have a basic understanding of names, instead of A1, I could refer to a cell
as Item if it were so named. Does the name in this case refer to a formula?
Also, unless an entry is located in the item column, the date column states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t have to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather than
the developer, who would need to leave __NOW__ undefined or initialized to
#N/A). Then the name __NOW__ could be used anywhere in any formula in the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date is
fine
as
the initial input to a cell, but I want to datestamp the dates on the
master
sold sheet so they do not update. Actually, I want to do this to the
whole
column so as dates get entered periodically, they do not change from the
inital value.

What do I need to do and thanks.

date stamp
 

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to advanced
excel
techniques and what you just said has gone a bit over my head. I was
hoping
for a cut and paste job but I might as well ask you to break it down for
me.
How exactly does this work? Do I just open the vb editor type this in? I
have a basic understanding of names, instead of A1, I could refer to a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t have to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather
than
the developer, who would need to leave __NOW__ undefined or initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date is
fine
as
the initial input to a cell, but I want to datestamp the dates on the
master
sold sheet so they do not update. Actually, I want to do this to the
whole
column so as dates get entered periodically, they do not change from
the
inital value.

What do I need to do and thanks.

date stamp
 

Thanks for all the help, I really appreciate it. The clock is a result of
me trying to see what gets updated in the workbook, I just forget to set it
back before posting.


"Tom Ogilvy" wrote in message
...
How about fixing your clock so you are not posting from the future.

--
Regards,
Tom Ogilvy

shawn modersohn wrote in message
...
Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

with
=IF(A2<0,DateAndTime())


"shawn modersohn" wrote in message
...
Perhaps I should have explained that I am just progressing to advanced
excel
techniques and what you just said has gone a bit over my head. I was
hoping
for a cut and paste job but I might as well ask you to break it down
for
me.
How exactly does this work? Do I just open the vb editor type this
in?
I
have a basic understanding of names, instead of A1, I could refer to a
cell
as Item if it were so named. Does the name in this case refer to a
formula?
Also, unless an entry is located in the item column, the date column
states
"false", which is necessary for the =IF(A2<0, 'formulas etc'!$D$14)
function to work. Does this change any considerations to your
solution.
Thank you very much.

"Bob Phillips" wrote in message
...
Shawn,

This came up recentlyu, and the best soluition IOMO was this one
from
Harlan
Grove. Obviously, you can name it whatever you want, it doesn'[t
have
to
be
__NOW__

=============================================
I'd have the Open event add the workbook-level name __NOW__ with the
date/time value when the workbook was first opened by a user (rather
than
the developer, who would need to leave __NOW__ undefined or
initialized
to
#N/A). Then the name __NOW__ could be used anywhere in any formula
in
the
workbook.


Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__NOW__")) Then _
Me.Names.Add Name:="__NOW__", RefersTo:=Now

CleanUp:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"shawn modersohn" wrote in message
...
I've got =IF(Inventory_list!C2="y", Inventory_list!A2, 'formulas
etc'!$D$9)
on a master sold sheet. d9 refers to a blank cell.

I've also got on the master sold sheet, =IF(A2<0, 'formulas
etc'!$D$14)
for
a date column. d14 refers to today's date. The d14 today's date
is
fine
as
the initial input to a cell, but I want to datestamp the dates on
the
master
sold sheet so they do not update. Actually, I want to do this to
the
whole
column so as dates get entered periodically, they do not change
from
the
inital value.

What do I need to do and thanks.