Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How can I return the weekday name from a date in VBA ?

Posted on 2014-03-14
5
Medium Priority
?
2,258 Views
Last Modified: 2014-03-15
Hi,

I'm trying to return the name of the day of the week from a date but I keep getting a compilation error on the "ddddd" part of the statement...

Dim lWeekday As Long
Dim strWeekdayText As Long

lWeekday = WorksheetFunction.Weekday(("teTrainingDate" & Idx))
strWeekdayText = WorksheetFunction.Text(lWeekday, “ddddd”)

Any ideas ?

Thanks
Toco
0
Comment
Question by:Tocogroup
  • 3
  • 2
5 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39929825
Hi,

the function Text in this case waits for a date
I do not see in your code that
"dddd" 4d only for the date in letters
You can use format as well

strWeekdayText = Format(yourDate, “dddd”)

Open in new window


EDIT I do not understand you frist line of code

How do you want to find a weekday with a "teTrainingDate" & Idx, it's no date

Regards
0
 

Author Comment

by:Tocogroup
ID: 39929884
Hi,

I'm getting a Run-time error 13 Type mismatch

Does the date have to be in a particular date format ?

strWeekdayText = WorksheetFunction.Text("teTrainingDate" & Idx, "ddddd")

My date is being held as dd-mmm-yyyy
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39929896
Hi,

what is the value of
"teTrainingDate" & Idx
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39930024
Hi,

You cannot refer to a variable via a string like in your code

pls try (add cases for your different dates indexes if necessary)

Dim lWeekday As Long
Dim strWeekdayText As String 'Edited
Dim strDate As String

Select Case Idx
    Case 1
        strDate = teTrainingDate1
    Case 2
        strDate = teTrainingDate2
    Case 3
        strDate = teTrainingDate3
    Case 4
        strDate = teTrainingDate4
        
    ' And so on
End Select
strWeekdayText = Format(strDate, "dddd")

Open in new window

Regards
0
 

Author Closing Comment

by:Tocogroup
ID: 39931137
Thanks for that
Toco
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

876 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question