Ozgrid Excel Newsletter. Excel Newsletter Archives  

Return Day Of Week Of Date

When finding the weekday associated with any date, most of us would rather see it returned as a name of the day, rather than as a number (the default).

Here we show how to extract the weekday of any date by using the WEEKDAY function. By default, the day is given as a whole number, ranging from 1 (Sunday) to 7 (Saturday). However, this is often meaningless, and we usually would rather see the weekday returned as a name, such as Monday/Mon, Tuesday/Tue, and so on.

Get the Weekday as a Number

Before getting to the name of the day, we'll need to begin by extracting the number of the weekday. Here's the syntax for the WEEKDAY formula:

WEEKDAY(serial_number,return_type)

Lets say we want to return the weekday number of 31-Jul-2007 The serial_num is any valid date in this case 31-Jul-2007 and the return_type is a number that refers to the type of return value. Depending on what result you are looking for will determine the return_type that you will use:

We will use the default function by omitting the return-type. Click in cell A1 and type in a valid Excel date, such as 31-Jul-2007 (which is a Tuesday). Then, click in cell B1 and enter the following formula:
=WEEKDAY(A1)

This formula will return the number 3, which equates to Tuesday, which is the day of the week that 31 July 2007 is.

An alternative would be to hardcode the date like this:
=WEEKDAY("31 Jul 2007�)

Return the Weekday as Weekday Name

Remember, the WEEKDAY formula shown in the previous section only returns the weekday as a number. There are at least two ways we can use formulas to force Excel to show the actual name of the weekday.

The first method is perhaps the simplest, and all you need to do is apply a custom number format of DDD or DDDD. Again using 31-Jul-2007 as an example, select the date cell, right-click, go to Format Cells, and then choose Number tab>Custom. Enter in the custom format DDD under Type and click OK. You will get Tue in your cell.

Another, probably safer way is to reference the date cell (e.g., =A1) and format this cell with a custom number format of DDD or DDDD. The big advantage to this method is that it leaves our true underlying date as a valid Excel date.
 

Return the Weekday as Weekday Text

If you won't be using the weekday that is returned in any further calculations, you can use either of the three formulas that follow (TEXT, WEEKDAY with CHOOSE) to return the weekday of a date as text.

This formula assumes you have a valid date of 31-Jul-2007 n cell A1:
=TEXT(A1,"DDDD")

will produce Tuesday, or you could hard code your formula like:
=TEXT("31 Jul 2007","DDDD")

you could use the slightly longer CHOOSE function to get the same result:
=CHOOSE(WEEKDAY(A1),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

and finally, you can hard code the date in the CHOOSE and nest the WEEKDAY function within it like:

=CHOOSE(WEEKDAY("31 July 2007"),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") ")

All of these formulas will return the same result - Tuesday, remember though the underlying value of your cell will still be 31-Jul-2007, even though your cell reads Tuesday.

EXCEL VBA: Changing text case

This month let's look at changing Case of letters. We'll look at UPPER CASE, Proper Case and lower case. UPPER CASE is the fastest because we can use the Replace Method, like this;

Sub UPPERCASE()
Dim lChr As Long

With Selection
   For lChr = 97 To 122
      .Replace Chr(lChr), UCase(Chr(lChr))
   Next lChr
End With
End Sub

If you are wondering how this works, let me explain. Chr(97) in VBA results in a lower case "a". Chr(98)="b", chr(99)="c" and so on....The Ucase Function is used to convert all lower case letters to UPPER CASE. "a" becomes "A" and so on...

For all other cases, we can use the StrConv Function, which can convert according to the table below;

vbUpperCase = Converts the string to uppercase characters.
vbLowerCase = Converts the string to lowercase characters.
vbProperCase = Converts the first letter of every word in string to uppercase.
vbWide = Converts narrow (single-byte) characters in string to wide (double-byte) characters.
vbNarrow = Converts wide (double-byte) characters in string to narrow (single-byte) characters.
vbKatakana = Converts Hiragana characters in string to Katakana characters.
vbHiragana = Converts Katakana characters in string to Hiragana characters.
vbUnicode = Converts the string to Unicode using the default code page of the system. (Not available on the Macintosh.)
vbFromUnicode = Converts the string from Unicode to the default code page of the system. (Not available on the Macintosh.)

The code we can use is;

Sub ConvertCase()
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long

    'Set variable to needed cells
    If Selection.Cells.Count = 1 Then
        Set rAcells = ActiveSheet.UsedRange
    Else
       Set rAcells = Selection
    End If

    On Error Resume Next 'In case of NO text constants.
    'Set variable to all text constants
    Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)

    If rAcells Is Nothing Then
       MsgBox "Could not find any text."
       On Error GoTo 0
       Exit Sub
    End If

    lReply = MsgBox("Select 'Yes' for lower case or 'No' for Proper Case.", _
    vbYesNoCancel, "OzGrid.com")

    If lReply = vbCancel Then Exit Sub


    If lReply = vbYes Then ' Convert to lower case
          For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
              rLoopCells = StrConv(rLoopCells, vbLowerCase)
          Next rLoopCells
    Else ' Convert to Proper Case
         For Each rLoopCells In rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
              rLoopCells = StrConv(rLoopCells, vbProperCase)
          Next rLoopCells
    End If

End Sub

Excel Dashboard Reports & Excel Dashboard Charts 50% Off

Become an ExcelUser Affiliate & Earn Money

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft