Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date through Macros
I am using macros in my excel sheets. Can anyone tell me how i can assign the
current date using macros. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
Use code like
ActiveCell.Value = Format(Now,"mm/dd/yyyy") -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "mrbalaje" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#4
|
|||
|
|||
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" wrote in message ... 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. |
#5
|
|||
|
|||
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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#6
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#7
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#8
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#9
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. -- Dave Peterson |
#10
|
|||
|
|||
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 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" wrote in message news:1A7457C7-96B9-4B6A-B72C- I get #VALUE! What am I doing wrong? |
#11
|
|||
|
|||
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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
#12
|
|||
|
|||
="Current date " & TEXT(TODAY(),"m/d/yyyy")
-- HTH RP (remove nothere from the email address if mailing direct) "mrbalaje" wrote in message ... 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" wrote in message ... I am using macros in my excel sheets. Can anyone tell me how i can assign the current date using macros. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Math Problem | Excel Worksheet Functions | |||
date and time | New Users to Excel | |||
Date issue between Windows and Macintosh version | Excel Discussion (Misc queries) | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |