date format


S

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!
 
Ad

Advertisements

J

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
 
D

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
 
R

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.
 
S

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?
 
S

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!
 
Ad

Advertisements

J

Jeff

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


Stephen said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top