Resolving Days of the Week to Calender Date

[Rollin_Again]

I have written a macro in Excel that pulls various information from
different reports on our network. The same macro is used to populate
data for both week-day reports as well as week-end reports. One of the
problems I am having is that the data for some days is stored in
different places than the other days and I need the macro to run
slightly different depending on what day it is. This is currently
accomplish with hard-coded "IF" statements that take into account the
actual calender date using *day(date)*. Based on what *day(date)*
returns, the code will execute one way or another.


Is there some function I can used to find out what day of the week
(Monday, Tues, etc) it currently is without having to take each of the
days of the month and match them to their respective days of the week
on a calender?

Rollin

 

[Norman Harker]

Hi Rollin_Again!

The WEEKDAY function returns the day of the week but as a number.

A chance effect of making the mistake of having a Leap Year in 1900 is
that if you format the cell ddd or dddd, you'll get the day of the
week displayed.

So you might use:

=TEXT(WEEKDAY(TODAY(),1),"dddd")
Returns the day of week of today as text.

This only works with the second argument of WEEKDAY as 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

Or maybe

=TEXT(TODAY(),"dddd")

--

Regards,

Peo Sjoblom

Hi Rollin_Again!

The WEEKDAY function returns the day of the week but as a number.

A chance effect of making the mistake of having a Leap Year in 1900 is
that if you format the cell ddd or dddd, you'll get the day of the
week displayed.

So you might use:

=TEXT(WEEKDAY(TODAY(),1),"dddd")
Returns the day of week of today as text.

This only works with the second argument of WEEKDAY as 1.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Peo Sjoblom]

Or just

=TODAY()

and format as dddd

--

Regards,

Peo Sjoblom

Or maybe

=TEXT(TODAY(),"dddd")

 

[Norman Harker]

Hi Peo!

=TEXT(TODAY(),"dddd") is better than the double function call and does
return text.

But TODAY() Formatted as dddd retains the underlying date number and
that might not be as appropriate as WEEKDAY(today()) which returns a
day number.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Rollin_Again]

Thanks for the quick responses. This function will be very useful for
me in the future but I have one other quick question. I can assign the
day of the week to a particular cell using the following VBA code
*Activecell.FormulaR1C1 = "=TEXT(TODAY(), ""dddd"")"* but how can I
assign the same value to a variable instead of a cell?



Rollin

 

[Daniel.M]

Hi,

I can assign the
day of the week to a particular cell using the following VBA code
*Activecell.FormulaR1C1 = "=TEXT(TODAY(), ""dddd"")"* but how can I
assign the same value to a variable instead of a cell?

myVar = Evaluate("=TEXT(TODAY(), ""dddd"")")

or, using VBA function:

myVar = Format(Date,"dddd") ' Date is the instruction for Today() in VBA

Regards,

Daniel M.