Unable to convert date to text

[Chris_Li]

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

 

[Fluff]

Hi & welcome to MrExcel.
Is this what you mean

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]

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]

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.