Unable to format DATE function

E

eckert1961

Registered User.
Local time
Today, 12:22
Joined
Oct 25, 2004
Messages
90
Hello,

I am running Excel 2003 and I want to use a macro that will enter the current date in the format dd-mmm-yy and the text UPDATE! into the selected cell. The result should look as follows.

04-Aug-08

UPDATE!

The problem that I run into is when I run the macro the date format changes to mm/dd/yyyy; ie 8/6/yyyy. Here is what my macro currently looks like.

ActiveCell.FormulaR1C1 = Date
ActiveCell.FormulaR1C1 = Date & Chr(10) & "" & Chr(10) & "UPDATE!"
Selection.NumberFormat = "dd-mmm-yy"

It appears that when you enter the date using a function and you enter text into the same cell you can't change the date format.

Would anyone know of a way to override this? Thanks in advance.

Regards,
Chris
 
eckert1961 said:
Hello,

I am running Excel 2003 and I want to use a macro that will enter the current date in the format dd-mmm-yy and the text UPDATE! into the selected cell. The result should look as follows.

04-Aug-08

UPDATE!

The problem that I run into is when I run the macro the date format changes to mm/dd/yyyy; ie 8/6/yyyy. Here is what my macro currently looks like.

ActiveCell.FormulaR1C1 = Date
ActiveCell.FormulaR1C1 = Date & Chr(10) & "" & Chr(10) & "UPDATE!"
Selection.NumberFormat = "dd-mmm-yy"

It appears that when you enter the date using a function and you enter text into the same cell you can't change the date format.

Would anyone know of a way to override this? Thanks in advance.

Regards,
Chris
Click to expand...

I think you need to change the Date() to a string before adding the text. Access has a cstr() function that does this. Does Excel have the same or a similar function?
 
TEXT Function

Thanks for the reply. I believe that I have to use the TEXT function to convert the DATE to a text string and then concatenate the text "UPDATE!" to it. Unfortunately I have no idea what the syntax should be. Additionally the cell that this will be entered will not always be the same one so I will probably have to use ActiveCell in my macro. Any ideas?

Chris
 
Progress

Well I managed to make some progress. Currently my macro is setup as follows.

ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""d-mmm-yyyy"")&"" "" & ""UPDATE!"""

This results in,

6-Aug-2008 UPDATE!

What I want the output to look like is,

6-Aug-2008

UPDATE!

I have tried to add linefeeds, chr(10), to the macro but this results in a #NAME? error.

The macro that gives me this error is,

ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""d-mmm-yyyy"") & Chr(10) & """" & Chr(10) & ""UPDATE!"""

Hopefully someone can point out what I'm missing.

Thanks,
Chris
 
Problem Solved

I finally figured it out. The following syntax did the trick.

ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""d-mmm-yy"")& CHAR(10) & CHAR(10) & ""UPDATE!"""

This gets me the desired result.

6-Aug-08

UPDATE!

Regards,
Chris
 
After the 2nd post I thought no he needs Text, however you were great and posted back saving me wrsetling with this, however when i run your solution I gon't get
"7-Aug-08

UPDATE!"

But 7-Aug-08 UPDATE!" separated by 2 little boxes, unprintable character symbols oe something I think.
Anybody able to tell me what's going wrong.
Brian
 
Brianwarnock said:
After the 2nd post I thought no he needs Text, however you were great and posted back saving me wrsetling with this, however when i run your solution I gon't get
"7-Aug-08

UPDATE!"

But 7-Aug-08 UPDATE!" separated by 2 little boxes, unprintable character symbols oe something I think.
Anybody able to tell me what's going wrong.
Brian
Click to expand...


The unprintable characters that you are referring to are the CHAR(10) & CHAR(10) that you added as part of his suggestion. I do something like this all of the time with addresses, and they display correctly in my forms.
 
MSAccessRookie said:
The unprintable characters that you are referring to are the CHAR(10) & CHAR(10) that you added as part of his suggestion. I do something like this all of the time with addresses, and they display correctly in my forms.
Click to expand...

I guessed that's what they were but why do they appear like that and not give me new lines as they do Chris, and me when I copy and paste the cell into a post. There must be something odd about my Excel settings.

The original attempts with Chr(10) worked ok from the new line angle.

Brian
 
Syntax?

Brian,

Can you post the syntax of your formula? I can have a look and see if anything jumps out at me.

Also, are you running Excel 2003?

Chris
 
Thanks for the offer Chris , however the formulae I used were identical yo yours as i was attempting to help before you solved your problem, I even copy and pasted.

I use 2002 SP3, but I wonder if that is the problem or some other setting somewhere.

BUT it is not really a problem at the moment as I'm unlikely to require to do this, tho' it would be nice to have a solution/reason, but please don't spend unnecessary time and effort.

Brian
 
The reason that the syntax isn't working for you could be the fact that you're running an older version of Excel than I am. Maybe someone in this forum who is also running the same version as you could verify this.

Good luck with it.

Chris
 
Text Formatting

Hello,

I was wondering if anyone knows if I can apply a different format to the date and the word UPDATE! in the following formula.

=TEXT(TODAY(),"d-mmm-yy")& CHAR(10) & CHAR(10) & "UPDATE!"

What I want is for the date to be dark blue and UPDATE! to be bolded and in a red font. Is this possible?

Thanks,
Chris
 
In Excel one formats at the cell level, atleast upto 2002 SP3 and I don't expect that to change, it's why you had to use the TEXT function, you cannot have a date and text mix in a cell, I also think thats why CHR(10) changed to CHAR(10), just don't know why I cannot get that to work.

It appears to me that you might be better redesigningwith an extra row.

Brian
 
Thanks for the reply Brian. I suspected that this wasn't possible but I thought I would ask. Having both date and text one font color isn't the end of my world so I will just leave it as is.

Take care,
Chris
 
Re: Text Formatting

eckert1961 said:
Hello,

I was wondering if anyone knows if I can apply a different format to the date and the word UPDATE! in the following formula.

=TEXT(TODAY(),"d-mmm-yy")& CHAR(10) & CHAR(10) & "UPDATE!"

What I want is for the date to be dark blue and UPDATE! to be bolded and in a red font. Is this possible?

Thanks,
Chris
Click to expand...

To change the style of the text within the same cell you need to use the .Characters(start,length) method... I think the following should work for you if the cell is still selected.

Code: 
    With ActiveCell.Characters(Start:=1, Length:=9).Font
        .FontStyle = "Regular"
        .ColorIndex = 5
    End With
    With ActiveCell.Characters(Start:=12, Length:=7).Font
        .FontStyle = "Bold"
        .Size = 12
        .ColorIndex = 3
    End With
 
The great thing about IT is that you can learn something new everyday.

Brian

EDIt It doesn't work on cells that are the result of Formula.
 
Last edited:
You must log in or register to reply here.

Similar threads

C
Date Filter not working
Replies
3
Views
387
Gasman
Gasman
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
362
Gasman
Gasman
D
Sorting Date imported as TEXT field in different formats
Replies
9
Views
662
arnelgp
arnelgp
B
Select from which date as a variable vba access
Replies
6
Views
209
Josef P.
J
S
datetime field conversion on access report
Replies
18
Views
938
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom