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

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

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?

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

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

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

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


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.

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.

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

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

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

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

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.

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.

EDIT It doesn't work on cells that are the result of Formula.

You are right! I should have checked that before posting!

(you really do learn something new every day! :))