date format

[Stephen]

Hi Folks,

I'm running a query and one of my columns returns a delivery date that is
formated as yyyy-mm-dd. Although the column is formatted as a general field,
I'm trying to changed the output to read Monday or Tuesday, etc. It seems
that due to the format of yyyy-mm-dd excel won't recognize this as a date
when I change the cells formatting.

I have a feeling I'm going to have to reformat the output to read mm/dd/yyyy
in order to then change it again to read Monday, Tuesday, etc. But I have
absolutely no idea where to start.

TIA!

 

[Jeff]

Try this

Public Function WeekdayName(ByVal aDate As Date) As String

aDate = Format(aDate, "yyyy-mm-dd")

Select Case Weekday(aDate)
Case 1
WeekdayName = "Sunday"
Case 2
WeekdayName = "Monday"
Case 3
WeekdayName = "Tuesday"
Case 4
WeekdayName = "Wednesday"
Case 5
WeekdayName = "Thursday"
Case 6
WeekdayName = "Friday"
Case 7
WeekdayName = "Saturday"
End Select
End Function

 

[Dave Peterson]

Your "dates" are plain old text.

Try converting them to real dates by:
Select the column
Data|Text to columns (in xl2003 menus)
Fixed width (but don't have any lines in the parsing window)
Choose Date (ymd)
And then finish up.

Then you should be able to reformat that column using any date format you want,
even:

dddd yyyy-mm-dd
or just plain
dddd

 

[Rick Rothstein]

There is already a WeekDayName function built into VB; it takes the day of
the week number as its argument. So, to use it with a Date argument, you
would do this...

NameOfDay = WeekdayName(Weekday(aDate))

where aDate is a Date data type. Another way to have done this would be...

NameOfDay = Format(aDate, "dddd")

where aDate is again a Date data type.

I'm not sure either of these will help the OP as he says his "date" is not
being recognized as a Date to Excel.

 

[Stephen]

No dice... unless I'm doing something wrong...

I added your function and call it the following way...

Range("B11").Select
WeekdayName

when I reach that point ini my execution I receive a vb error "Compile
error: Arguement not optional"

any thoughts?

 

[Stephen]

brilliant! worked like a charm...

Here's what I ended up using...

Range("B11:B65536").Select
Selection.TextToColumns Destination:=Range("B11:B65536"),
DataType:=xlDelimited, _
TextQualifier:=xlNone, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
:="-", FieldInfo:=Array(1, 3), TrailingMinusNumbers:=True
Selection.NumberFormat = "dddd"

My range will always start at B11.

Thank you very much!

 

[Jeff]

That should be weekdayName(Range("B11").value)

No dice... unless I'm doing something wrong...

I added your function and call it the following way...

Range("B11").Select
WeekdayName

when I reach that point ini my execution I receive a vb error "Compile
error: Arguement not optional"

any thoughts?