VBA date to day text

gripper

Board Regular
Joined
Oct 29, 2002
Messages
164
Hi Guys,

Trying to solve an issue with my VBA coding skills.

I have a sheet with multiple columns. The raw data I have a date in column "A". With the below procedures I insert a new blank column into "A" pushing everything to the left one column. I then insert "DOW" (meaning Day of Week" into A1. That works fine.

The second part of this code is I want to loop down through this column and if blank (which it will be) it will interpret the date ( formatted 12/22/2020) in column "B" on that row and make the cell in (i,1) equal to the date in (i,2) and make it a word. (example "Mon, Tue, Wed" etc)

When I step through the code I get a syntax error at line " Cells(i,1) = Cells((i,2),(Text,""ddd"")) " and the code stops.

I want it to loop through the entire column which is about 1000 records and add this day notation.

Thank you for reviewing and offering up a solution.



VBA Code:
Sub formatsheet()

Dim lrow, i As Long

lrow = Cells(Rows.Count, "B").End(xlUp).Row
i = 2

'Insert into Column A "DOW" and run

Columns("A:A").Select
Selection.Insert Shift:=xlToRight
ActiveCell.Value = "DOW"
Do While i <= lrow
  If Cells(i, 1).Value = "" Then
  Cells(i,1) = Cells((i,2),(Text,""ddd""))
 
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
"Text" is an Excel worksheet function. The VBA equivalane is "Format", and you need to structure it the same way as "Text", i.e.
VBA Code:
Cells(i,1) = Format(Cells(i,2),"ddd")
 
Solution

gripper

Board Regular
Joined
Oct 29, 2002
Messages
164
Joe4

Thank you. That did the trick.

Don't know if this is the most efficient (fast) way to do this but your syntax fix worked.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
Don't know if this is the most efficient (fast) way to do this but your syntax fix worked.
A lot of it depends on what you are using it for, and if those values REALLY need to hold those values, or just display those values.

For example, if you had a date in cell A1, you could also use this simple Excel function to get the same thing that our VBA code was:
=TEXT(A1,"ddd")

Note that both that solution and the VBA one proposed will return Text values.

If you just need the appearance to change, in the example above, you could simply put this in B1:
=A1
and apply a Custom Format to the cell of ddd

The difference there is the value in B1 is actually a date (not Text), just with a Custom Format.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,914
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,437
Messages
5,596,113
Members
414,043
Latest member
thomas Stein

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
Top