if Then Day

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
I am wondering if there is an easier way to return the first letter of the weekday in VBA other than what what I have here.

For X = 1 to 1000

If cells(x,2) = "Monday" then
cells(x,3) = "M")
Elseif cells(x,2 = "Tuesday" then
cells(x,3) = "T" and on to Sunday...

Thanks
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Code:
Sub v()
[C1:C1000] = [INDEX(LEFT(TEXT(B1:B1000,"ddd"),1),)]
End Sub

If none of the cells in B1:B1000 is formatted as Date, then this shorter version could be used :
Code:
Sub v()
[C1:C1000] = [INDEX(LEFT(B1:B1000,1),)]
End Sub
 
Last edited:
Upvote 0
Correction to first code - to handle blank cells :

Code:
Sub vv()
[C1:C1000] = Evaluate("=if(LEN(B1:B1000),LEFT(TEXT(B1:B1000,""ddd""),1),"""")")
End Sub
 
Upvote 0
Try this:
Code:
Sub Check_Weekday()
Application.ScreenUpdating = False
Dim i As Long
    For i = 1 To 1000
        MyVal = Cells(i, 2).Value
            Select Case MyVal
                Case "Sunday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Monday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Tuesday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Wednesday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Thursday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Friday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
                Case "Saturday"
                    Cells(i, 3).Value = Left(Cells(i, 2), 1)
            End Select
        
    Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
My answer is this

The case code worked well. Only thing I did not mention I would need to change Sunday to a unique value different from Saturday.
 
Upvote 0
My answer is this

The case code worked well. Only thing I did not mention I would need to change Sunday to a unique value different from Saturday.

You want to change "Sunday" to what?
Do not understand unique value different from "Saturday"
 
Upvote 0
@billandrew
Did you try the macro I posted? :

Code:
Sub v()
[C1:C1000] = [INDEX(LEFT(B1:B1000,1),)]
End Sub
 
Upvote 0
Hi
if you want different values for Saturday & Sunday y not take the first 2 characters
Code:
Sub Day()

    Dim i As Long
    
    For i = 1 To 1000
        Range("C" & i) = Left(Range("B" & i), 2)
    Next i
    
End Sub
 
Upvote 0
My answer is this

The case code worked well. Only thing I did not mention I would need to change Sunday to a unique value different from Saturday.

Code:
Sub v()
Dim r$: r = [B1:B1000].Address
[C1:C1000] = Evaluate(Replace("=if(#=""Sunday"",""$"",LEFT(#,1))", "#", r))
End Sub

Or if you want to show two letters instead of one :

Code:
Sub v()
[C1:C1000] = [INDEX(LEFT(B1:B1000,2),)]
End Sub
 
Last edited:
Upvote 0
My answer is this

The case code worked well. Only thing I did not mention I would need to change Sunday to a unique value different from Saturday.

What about Tuesday and Thursday ?
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top