Solved

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

Posted on 2014-03-14
5
1,454 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 48

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 48

Expert Comment

by:Rgonzo1971
ID: 39929896
Hi,

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

Accepted Solution

by:
Rgonzo1971 earned 500 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now