Date through Macros

I am using macros in my excel sheets. Can anyone tell me how i can assign the
current date using macros.

Hi!

Try the following:

Selection.Value = "=NOW()"
Selection.NumberFormat = "ddd dd-mmm-yy"
Selection.Formula = Selection.Value

The first line puts the =now() formula into the currently selected cell
The second line formats the entry to Mon 21-Mar-05 etc
The third line converts =now() to a value, otherwise, tomorrow when you open
the workbook, the formula will show tomorrow's date!

Hope this helps

Pete



"mrbalaje" wrote:

> I am using macros in my excel sheets. Can anyone tell me how i can assign the
> current date using macros.

Use code like

ActiveCell.Value = Format(Now,"mm/dd/yyyy")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





"mrbalaje" <[email protected]> wrote in message
news:[email protected]...
>I am using macros in my excel sheets. Can anyone tell me how i
>can assign the
> current date using macros.

If you want a static date, i.e. not updated tomorrow, try

Selection.Value = Date

or formatted

Selection.Value = Format(Date,"dd mmm yyyyy")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try the following:
>
> Selection.Value = "=NOW()"
> Selection.NumberFormat = "ddd dd-mmm-yy"
> Selection.Formula = Selection.Value
>
> The first line puts the =now() formula into the currently selected cell
> The second line formats the entry to Mon 21-Mar-05 etc
> The third line converts =now() to a value, otherwise, tomorrow when you
open
> the workbook, the formula will show tomorrow's date!
>
> Hope this helps
>
> Pete
>
>
>
> "mrbalaje" wrote:
>
> > I am using macros in my excel sheets. Can anyone tell me how i can
assign the
> > current date using macros.

Chip,

The only problem with this is it returns a label, which isn't helpful if you
want to do date maths (I know this contributor didn't say he wanted to, but I
do quite a lot)
My suggestion, although a bit unwieldy, does give you a "valued" and
"mathable" date.
By the way, your web site is superb! Keep up the good work!

Pete (waiting to be proved wrong on the above...)



"Chip Pearson" wrote:

> Use code like
>
> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "mrbalaje" <[email protected]> wrote in message
> news:[email protected]...
> >I am using macros in my excel sheets. Can anyone tell me how i
> >can assign the
> > current date using macros.
>
>
>

Peter,

You can certainly do date arithmetic on the date value in the
cell.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Peter Rooney" <[email protected]> wrote in
message
news:[email protected]...
> Chip,
>
> The only problem with this is it returns a label, which isn't
> helpful if you
> want to do date maths (I know this contributor didn't say he
> wanted to, but I
> do quite a lot)
> My suggestion, although a bit unwieldy, does give you a
> "valued" and
> "mathable" date.
> By the way, your web site is superb! Keep up the good work!
>
> Pete (waiting to be proved wrong on the above...)
>
>
>
> "Chip Pearson" wrote:
>
>> Use code like
>>
>> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>>
>> "mrbalaje" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >I am using macros in my excel sheets. Can anyone tell me how
>> >i
>> >can assign the
>> > current date using macros.
>>
>>
>>

Chip,

Talk about setting myself up for a fall..!

....except that I can't make it work. If I subtract a cell containing a
formula created in this way from a cell with a non-string date in it, I get
#VALUE!

If I subtract the cell from a cell with another string date in it, I get the
same thing.

And if I use datevalue(a1)-datevalue(a2) where both cells contain string
dates, I get the same thing too.

What am I doing wrong?

And to think this started off as someone ELSE's problem :o)

Pete




"Chip Pearson" wrote:

> Peter,
>
> You can certainly do date arithmetic on the date value in the
> cell.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Peter Rooney" <[email protected]> wrote in
> message
> news:[email protected]...
> > Chip,
> >
> > The only problem with this is it returns a label, which isn't
> > helpful if you
> > want to do date maths (I know this contributor didn't say he
> > wanted to, but I
> > do quite a lot)
> > My suggestion, although a bit unwieldy, does give you a
> > "valued" and
> > "mathable" date.
> > By the way, your web site is superb! Keep up the good work!
> >
> > Pete (waiting to be proved wrong on the above...)
> >
> >
> >
> > "Chip Pearson" wrote:
> >
> >> Use code like
> >>
> >> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >>
> >>
> >>
> >> "mrbalaje" <[email protected]> wrote in
> >> message
> >> news:[email protected]...
> >> >I am using macros in my excel sheets. Can anyone tell me how
> >> >i
> >> >can assign the
> >> > current date using macros.
> >>
> >>
> >>
>
>
>

It's not a formula created this way, it is just a date value.and so it will
work the same as if you input say 1/1/2005 directly. SO don't work with
another string date (whatever you mean by that), but just a standard date.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Chip,
>
> Talk about setting myself up for a fall..!
>
> ...except that I can't make it work. If I subtract a cell containing a
> formula created in this way from a cell with a non-string date in it, I
get
> #VALUE!
>
> If I subtract the cell from a cell with another string date in it, I get
the
> same thing.
>
> And if I use datevalue(a1)-datevalue(a2) where both cells contain string
> dates, I get the same thing too.
>
> What am I doing wrong?
>
> And to think this started off as someone ELSE's problem :o)
>
> Pete
>
>
>
>
> "Chip Pearson" wrote:
>
> > Peter,
> >
> > You can certainly do date arithmetic on the date value in the
> > cell.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> > "Peter Rooney" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > > Chip,
> > >
> > > The only problem with this is it returns a label, which isn't
> > > helpful if you
> > > want to do date maths (I know this contributor didn't say he
> > > wanted to, but I
> > > do quite a lot)
> > > My suggestion, although a bit unwieldy, does give you a
> > > "valued" and
> > > "mathable" date.
> > > By the way, your web site is superb! Keep up the good work!
> > >
> > > Pete (waiting to be proved wrong on the above...)
> > >
> > >
> > >
> > > "Chip Pearson" wrote:
> > >
> > >> Use code like
> > >>
> > >> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
> > >>
> > >>
> > >> --
> > >> Cordially,
> > >> Chip Pearson
> > >> Microsoft MVP - Excel
> > >> Pearson Software Consulting, LLC
> > >> www.cpearson.com
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "mrbalaje" <[email protected]> wrote in
> > >> message
> > >> news:[email protected]...
> > >> >I am using macros in my excel sheets. Can anyone tell me how
> > >> >i
> > >> >can assign the
> > >> > current date using macros.
> > >>
> > >>
> > >>
> >
> >
> >

Try:
=a1-a2
if both are just plain old dates.

Format the cell as General (or some number format).




Peter Rooney wrote:
>
> Chip,
>
> Talk about setting myself up for a fall..!
>
> ...except that I can't make it work. If I subtract a cell containing a
> formula created in this way from a cell with a non-string date in it, I get
> #VALUE!
>
> If I subtract the cell from a cell with another string date in it, I get the
> same thing.
>
> And if I use datevalue(a1)-datevalue(a2) where both cells contain string
> dates, I get the same thing too.
>
> What am I doing wrong?
>
> And to think this started off as someone ELSE's problem :o)
>
> Pete
>
> "Chip Pearson" wrote:
>
> > Peter,
> >
> > You can certainly do date arithmetic on the date value in the
> > cell.
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> > "Peter Rooney" <[email protected]> wrote in
> > message
> > news:[email protected]...
> > > Chip,
> > >
> > > The only problem with this is it returns a label, which isn't
> > > helpful if you
> > > want to do date maths (I know this contributor didn't say he
> > > wanted to, but I
> > > do quite a lot)
> > > My suggestion, although a bit unwieldy, does give you a
> > > "valued" and
> > > "mathable" date.
> > > By the way, your web site is superb! Keep up the good work!
> > >
> > > Pete (waiting to be proved wrong on the above...)
> > >
> > >
> > >
> > > "Chip Pearson" wrote:
> > >
> > >> Use code like
> > >>
> > >> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
> > >>
> > >>
> > >> --
> > >> Cordially,
> > >> Chip Pearson
> > >> Microsoft MVP - Excel
> > >> Pearson Software Consulting, LLC
> > >> www.cpearson.com
> > >>
> > >>
> > >>
> > >>
> > >>
> > >> "mrbalaje" <[email protected]> wrote in
> > >> message
> > >> news:[email protected]...
> > >> >I am using macros in my excel sheets. Can anyone tell me how
> > >> >i
> > >> >can assign the
> > >> > current date using macros.
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson

Hi Peter,
Probably the difficulty is with getting an answer here is not indicating
exactly what you have for a formula and what is displayed in each cell,
and what you have in your macro.

You will get a #VALUE! on your worksheet has leading spaces in
the date for instance. It is not a number and would not by default
be right aligned. If the data came from HTML they might be
non-breakijg-space characters &nbsp;

Try making a copy of the worksheet and run the TRIMALL macro
on your date columns.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
if that does not work then try examining your data with the
functions like e ISNUMBER or ISTEXT additional things to
check are in the comments above the TRIMALL macro on that page.

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Peter Rooney" <[email protected]> wrote in message news:1A7457C7-96B9-4B6A-B72C-
> I get #VALUE!
>
> What am I doing wrong?

Suppose in cell A1 there is already a text like "Date Audited".
So I want the current date, that will change every day to be imposed on the
same cell.

Example"
In cell A1:

"Current date 4/18/2005"

In the above the text "current date" is static whereas the date is dynamic.
Can it be done through the macros.



"Chip Pearson" wrote:

> Use code like
>
> ActiveCell.Value = Format(Now,"mm/dd/yyyy")
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
>
> "mrbalaje" <[email protected]> wrote in message
> news:[email protected]...
> >I am using macros in my excel sheets. Can anyone tell me how i
> >can assign the
> > current date using macros.
>
>
>

="Current date " & TEXT(TODAY(),"m/d/yyyy")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mrbalaje" <[email protected]> wrote in message
news:[email protected]...
> Suppose in cell A1 there is already a text like "Date Audited".
> So I want the current date, that will change every day to be imposed on
the
> same cell.
>
> Example"
> In cell A1:
>
> "Current date 4/18/2005"
>
> In the above the text "current date" is static whereas the date is
dynamic.
> Can it be done through the macros.
>
>
>
> "Chip Pearson" wrote:
>
> > Use code like
> >
> > ActiveCell.Value = Format(Now,"mm/dd/yyyy")
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> >
> >
> >
> > "mrbalaje" <[email protected]> wrote in message
> > news:[email protected]...
> > >I am using macros in my excel sheets. Can anyone tell me how i
> > >can assign the
> > > current date using macros.
> >
> >
> >