Unable to convert date to text

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Excel Community
I tried to find a solution on several boards but it seems I can't find it. Perhaps you can help me. First you need to know: I'm a noob regarding VBA.

Here the problem.
I have an excel file with thousands of rows and the column have headers.
In Column E I have dates with the format: dd/mm/yyyy. The range is between 5000 days in the past and 360 days in the future.
I need in column S the date format changed into text. So that e.g. 01/03/2021 would tell me: Monday

I have recorded a macro which resulted in this:


Range("S2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-14],""DDDD"")"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S25000"), Type:=xlFillDefault
Range("S2:S25000").Select

That is working but I rather would prefer something like this:

Dim LastRowColumnA As Long
Dim lr As Long, r As Long

Range("T2").Select

LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("T2:T" & LastRowColumnA).Formula = "=IF(OR(F2=-12, F2=-11, F2=-10),""Yes"",""No"")"

Here I let Excel check for a date range (12, 11 and 10 days in the future as this is the crucial time frame)
It checks all rows and enters "Yes" or "No" depending on the conditions it finds in column F.

The file I create every day is changing in his size. So I don't have exactly 25k rows (as entered in my recorded macro). It can be more or less. To avoid extra work I would prefer that the macro is checking and filling in the date as a text in each cell of column S until the last row.

Can you please guide me through what I need to do?

Let me know if something is unclear.

Thanks for your time and effort.

Chris_Li
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,891
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Is this what you mean
VBA Code:
Dim LastRowColumnA As Long
Dim lr As Long, r As Long


LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("S2:S" & LastRowColumnA).FormulaR1C1 = "=TEXT(RC[-14],""DDDD"")"
Range("T2:T" & LastRowColumnA).Formula = "=IF(OR(F2=-12, F2=-11, F2=-10),""Yes"",""No"")"
 

Chris_Li

New Member
Joined
Mar 1, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Fluff,

wow, thank you. It works like a charm. If not to much, could you please explain to me: this: .FormulaR1C1
Why am I not using it in the second Range row?

Thank you very much again

Cheers

Chris_Li
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,891
Office Version
  1. 365
Platform
  1. Windows
Why am I not using it in the second Range row?
Because the formula is in A1 notation, whereas your first formula was in R1C1 notation.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

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