date stamp

[Bob Phillips]

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)

 

[Bob Phillips]

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)

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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

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.

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

 

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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

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.

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

 

[Rob van Gelder]

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

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.

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

--
Regards,
Tom Ogilvy

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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


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.

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)

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 on
the to
the

 

[shawn modersohn]

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

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)

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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


for
me.

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.

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 initialized in
the from
the

 

[shawn modersohn]

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.

 

[shawn modersohn]

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.

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)

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.

 

[shawn modersohn]

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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

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.

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)

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.

 

[shawn modersohn]

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.

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

--
Regards,
Tom Ogilvy

Jackpot, I found something that works.

Function DateAndTime()
DateAndTime = Now
End Function

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


for
me.

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.

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 initialized in
the from
the