Insert Date that Macro runs

Is there anyway that I can have a macro insert a date into a column. I would like to the date and time to show when the macro was run.

Activecell.offset(0,5).value = (actual date and time of when macro was run)

Use

Activecell.offset(0,5).value = Now

HTH

Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")

--
HTH

Bob Phillips

"bhofsetz" <[email protected]> wrote in
message news:[email protected]...
>
> Use
>
> Activecell.offset(0,5).value = Now
>
> HTH
>
>
> --
> bhofsetz
> ------------------------------------------------------------------------
> bhofsetz's Profile:
http://www.excelforum.com/member.php...o&userid=18807
> View this thread: http://www.excelforum.com/showthread...hreadid=385675
>

Is there any way to replace part of a date within a macro? I want to change
the month. The syntax is "1/1/2004" I want to have it replaced with
"2/1/2004" but the only thing I need is to update the month in all occurences
of "1/xxx/2004" Right now I am manually editing each line to change a
1/1/2004 to a 2/1/2004.

"Bob Phillips" wrote:

> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
>
> --
> HTH
>
> Bob Phillips
>
> "bhofsetz" <[email protected]> wrote in
> message news:[email protected]...
> >
> > Use
> >
> > Activecell.offset(0,5).value = Now
> >
> > HTH
> >
> >
> > --
> > bhofsetz
> > ------------------------------------------------------------------------
> > bhofsetz's Profile:
> http://www.excelforum.com/member.php...o&userid=18807
> > View this thread: http://www.excelforum.com/showthread...hreadid=385675
> >
>
>
>

The general formula in code
could be something like:

Dim yr as Integer, mnth as Integer, dy as Integer
yr = year(ActiveCell)
mnth = month(ActiveCell)+1
dy = day(ActiveCell)

ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)


but if mnth = 13 it must be reset to 1 and yr = yr +1

--
steveB

Remove "AYN" from email to respond
"lschuh" <[email protected]> wrote in message
news:[email protected]...
> Is there any way to replace part of a date within a macro? I want to
> change
> the month. The syntax is "1/1/2004" I want to have it replaced with
> "2/1/2004" but the only thing I need is to update the month in all
> occurences
> of "1/xxx/2004" Right now I am manually editing each line to change a
> 1/1/2004 to a 2/1/2004.
>
> "Bob Phillips" wrote:
>
>> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> "bhofsetz" <[email protected]> wrote
>> in
>> message news:[email protected]...
>> >
>> > Use
>> >
>> > Activecell.offset(0,5).value = Now
>> >
>> > HTH
>> >
>> >
>> > --
>> > bhofsetz
>> > ------------------------------------------------------------------------
>> > bhofsetz's Profile:
>> http://www.excelforum.com/member.php...o&userid=18807
>> > View this thread:
>> > http://www.excelforum.com/showthread...hreadid=385675
>> >
>>
>>
>>

Thank you for the code I am going to print it out. It might sound elementary
but would you place this in the macro you are changing or create a new macro
that references the macro to change?

I probably wrote 10 messages here and in MSAccess discussion groups and I
finally figured out how to replace the month with the replace function. The
only thing I have to change is the occurences where it changes not only the
month but the day to. Appreciate the help.

"STEVE BELL" wrote:

> The general formula in code
> could be something like:
>
> Dim yr as Integer, mnth as Integer, dy as Integer
> yr = year(ActiveCell)
> mnth = month(ActiveCell)+1
> dy = day(ActiveCell)
>
> ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)
>
>
> but if mnth = 13 it must be reset to 1 and yr = yr +1
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > Is there any way to replace part of a date within a macro? I want to
> > change
> > the month. The syntax is "1/1/2004" I want to have it replaced with
> > "2/1/2004" but the only thing I need is to update the month in all
> > occurences
> > of "1/xxx/2004" Right now I am manually editing each line to change a
> > 1/1/2004 to a 2/1/2004.
> >
> > "Bob Phillips" wrote:
> >
> >> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
> >>
> >> --
> >> HTH
> >>
> >> Bob Phillips
> >>
> >> "bhofsetz" <[email protected]> wrote
> >> in
> >> message news:[email protected]...
> >> >
> >> > Use
> >> >
> >> > Activecell.offset(0,5).value = Now
> >> >
> >> > HTH
> >> >
> >> >
> >> > --
> >> > bhofsetz
> >> > ------------------------------------------------------------------------
> >> > bhofsetz's Profile:
> >> http://www.excelforum.com/member.php...o&userid=18807
> >> > View this thread:
> >> > http://www.excelforum.com/showthread...hreadid=385675
> >> >
> >>
> >>
> >>
>
>
>

You're very Welcome!

Put the code where it works best. The choice is always yours...

And you are finding that there are many ways to get there from here...

Making a separate module can simplify your code, make the procedure
available to any other module, and make modifying easier.

Keep on Exceling...

--
steveB

Remove "AYN" from email to respond
"lschuh" <[email protected]> wrote in message
news:[email protected]...
> Thank you for the code I am going to print it out. It might sound
> elementary
> but would you place this in the macro you are changing or create a new
> macro
> that references the macro to change?
>
> I probably wrote 10 messages here and in MSAccess discussion groups and I
> finally figured out how to replace the month with the replace function.
> The
> only thing I have to change is the occurences where it changes not only
> the
> month but the day to. Appreciate the help.
>
> "STEVE BELL" wrote:
>
>> The general formula in code
>> could be something like:
>>
>> Dim yr as Integer, mnth as Integer, dy as Integer
>> yr = year(ActiveCell)
>> mnth = month(ActiveCell)+1
>> dy = day(ActiveCell)
>>
>> ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)
>>
>>
>> but if mnth = 13 it must be reset to 1 and yr = yr +1
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "lschuh" <[email protected]> wrote in message
>> news:[email protected]...
>> > Is there any way to replace part of a date within a macro? I want to
>> > change
>> > the month. The syntax is "1/1/2004" I want to have it replaced
>> > with
>> > "2/1/2004" but the only thing I need is to update the month in all
>> > occurences
>> > of "1/xxx/2004" Right now I am manually editing each line to change a
>> > 1/1/2004 to a 2/1/2004.
>> >
>> > "Bob Phillips" wrote:
>> >
>> >> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Bob Phillips
>> >>
>> >> "bhofsetz" <[email protected]>
>> >> wrote
>> >> in
>> >> message news:[email protected]...
>> >> >
>> >> > Use
>> >> >
>> >> > Activecell.offset(0,5).value = Now
>> >> >
>> >> > HTH
>> >> >
>> >> >
>> >> > --
>> >> > bhofsetz
>> >> > ------------------------------------------------------------------------
>> >> > bhofsetz's Profile:
>> >> http://www.excelforum.com/member.php...o&userid=18807
>> >> > View this thread:
>> >> > http://www.excelforum.com/showthread...hreadid=385675
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

I ran that code. Pretty cool. Now I need to figure out what I can use it for.

"STEVE BELL" wrote:

> You're very Welcome!
>
> Put the code where it works best. The choice is always yours...
>
> And you are finding that there are many ways to get there from here...
>
> Making a separate module can simplify your code, make the procedure
> available to any other module, and make modifying easier.
>
> Keep on Exceling...
>
> --
> steveB
>
> Remove "AYN" from email to respond
> "lschuh" <[email protected]> wrote in message
> news:[email protected]...
> > Thank you for the code I am going to print it out. It might sound
> > elementary
> > but would you place this in the macro you are changing or create a new
> > macro
> > that references the macro to change?
> >
> > I probably wrote 10 messages here and in MSAccess discussion groups and I
> > finally figured out how to replace the month with the replace function.
> > The
> > only thing I have to change is the occurences where it changes not only
> > the
> > month but the day to. Appreciate the help.
> >
> > "STEVE BELL" wrote:
> >
> >> The general formula in code
> >> could be something like:
> >>
> >> Dim yr as Integer, mnth as Integer, dy as Integer
> >> yr = year(ActiveCell)
> >> mnth = month(ActiveCell)+1
> >> dy = day(ActiveCell)
> >>
> >> ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)
> >>
> >>
> >> but if mnth = 13 it must be reset to 1 and yr = yr +1
> >>
> >> --
> >> steveB
> >>
> >> Remove "AYN" from email to respond
> >> "lschuh" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Is there any way to replace part of a date within a macro? I want to
> >> > change
> >> > the month. The syntax is "1/1/2004" I want to have it replaced
> >> > with
> >> > "2/1/2004" but the only thing I need is to update the month in all
> >> > occurences
> >> > of "1/xxx/2004" Right now I am manually editing each line to change a
> >> > 1/1/2004 to a 2/1/2004.
> >> >
> >> > "Bob Phillips" wrote:
> >> >
> >> >> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
> >> >>
> >> >> --
> >> >> HTH
> >> >>
> >> >> Bob Phillips
> >> >>
> >> >> "bhofsetz" <[email protected]>
> >> >> wrote
> >> >> in
> >> >> message news:[email protected]...
> >> >> >
> >> >> > Use
> >> >> >
> >> >> > Activecell.offset(0,5).value = Now
> >> >> >
> >> >> > HTH
> >> >> >
> >> >> >
> >> >> > --
> >> >> > bhofsetz
> >> >> > ------------------------------------------------------------------------
> >> >> > bhofsetz's Profile:
> >> >> http://www.excelforum.com/member.php...o&userid=18807
> >> >> > View this thread:
> >> >> > http://www.excelforum.com/showthread...hreadid=385675
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

I just set up a form to scan through a list of dates and change the dates.
(My dates got screwed up)

The form checks each cell for the month #, day #, and year #.
Than I make selections from separate drop-downs for the revised month #, day
#, and year #
and replace the date in the cell using the DateSerial formula.

This allows me to go through a long list and correct all the dates
(especially since all the dates are previous to 2005 - thus preventing me
from just entering m/d)

The code I sent you does something similar. It looks at the date and adds
one month to it. (It also accounts for going from Dec to Jan by changing
the year. Than puts the new date into another cell.

--
steveB

Remove "AYN" from email to respond
"lschuh" <[email protected]> wrote in message
news:[email protected]...
>I ran that code. Pretty cool. Now I need to figure out what I can use it
>for.
>
> "STEVE BELL" wrote:
>
>> You're very Welcome!
>>
>> Put the code where it works best. The choice is always yours...
>>
>> And you are finding that there are many ways to get there from here...
>>
>> Making a separate module can simplify your code, make the procedure
>> available to any other module, and make modifying easier.
>>
>> Keep on Exceling...
>>
>> --
>> steveB
>>
>> Remove "AYN" from email to respond
>> "lschuh" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thank you for the code I am going to print it out. It might sound
>> > elementary
>> > but would you place this in the macro you are changing or create a new
>> > macro
>> > that references the macro to change?
>> >
>> > I probably wrote 10 messages here and in MSAccess discussion groups and
>> > I
>> > finally figured out how to replace the month with the replace function.
>> > The
>> > only thing I have to change is the occurences where it changes not only
>> > the
>> > month but the day to. Appreciate the help.
>> >
>> > "STEVE BELL" wrote:
>> >
>> >> The general formula in code
>> >> could be something like:
>> >>
>> >> Dim yr as Integer, mnth as Integer, dy as Integer
>> >> yr = year(ActiveCell)
>> >> mnth = month(ActiveCell)+1
>> >> dy = day(ActiveCell)
>> >>
>> >> ActiveCell.Offset(0,5)=DateSerial(yr, mnth, dy)
>> >>
>> >>
>> >> but if mnth = 13 it must be reset to 1 and yr = yr +1
>> >>
>> >> --
>> >> steveB
>> >>
>> >> Remove "AYN" from email to respond
>> >> "lschuh" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Is there any way to replace part of a date within a macro? I want
>> >> > to
>> >> > change
>> >> > the month. The syntax is "1/1/2004" I want to have it
>> >> > replaced
>> >> > with
>> >> > "2/1/2004" but the only thing I need is to update the month in all
>> >> > occurences
>> >> > of "1/xxx/2004" Right now I am manually editing each line to change
>> >> > a
>> >> > 1/1/2004 to a 2/1/2004.
>> >> >
>> >> > "Bob Phillips" wrote:
>> >> >
>> >> >> Activecell.offset(0,5).value = Format(Now,"dd mmm yyyy hh:mm:ss")
>> >> >>
>> >> >> --
>> >> >> HTH
>> >> >>
>> >> >> Bob Phillips
>> >> >>
>> >> >> "bhofsetz" <[email protected]>
>> >> >> wrote
>> >> >> in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> >
>> >> >> > Use
>> >> >> >
>> >> >> > Activecell.offset(0,5).value = Now
>> >> >> >
>> >> >> > HTH
>> >> >> >
>> >> >> >
>> >> >> > --
>> >> >> > bhofsetz
>> >> >> > ------------------------------------------------------------------------
>> >> >> > bhofsetz's Profile:
>> >> >> http://www.excelforum.com/member.php...o&userid=18807
>> >> >> > View this thread:
>> >> >> > http://www.excelforum.com/showthread...hreadid=385675
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>