Get name from date in VBA

[tfagan1978]

Hi all,

I am looking to get the name of the day from a date using VBA in Excel. I am using this formula but it is not working. Can anyone help?

Please Login or Register  to view this content.

Range("B10:B200").Formula = "=IF(A10="""","""",WEKKDAYNAME(A10, "dddd"))"

[LewisJ]

Weekdayname?

[LewisJ]

=TEXT(A10,"dddd")

copy it down

[LewisJ]

To ensure no blanks use:

=IF(A10<>0,TEXT(A10,"dddd"),"")

[tfagan1978]

I had originally tried that but it doesn't seem to work in VBA. I keep getting a Syntax error. I'm thinking that "Text" is not able to be used in VBA. It works fine as a normal formula in Excel but not in a VBA code

[Phuocam]

Try this ...

Range("B10:B200").FormulaR1C1 = "=IF(RC[-1]="""","""",TEXT(RC[-1],""dddd""))"

[Zer0Cool]

There is a built in VBA function for this.

WeekDayName

Takes 3 args. Day of the week index number (1-7), True/false for abbreviated name, and index number for first day of the week.

You can combine this with the VBA Weekday function. The Weekday function returns the index number representing the day of the week from a date. 2 args, date and an index number or vbConst representing what day of the week starts a week.

Together they would look like:

Please Login or Register  to view this content.

Instead of debug.print you could change this to a variable to store the day of the week or set it as a the value of a range object.

If you need to use it for a column of cells you could loop the cells and use the date from a cell as the date argument of weekday.

Hope this helps