Using date variable in vba formula

Hi,
I've been unsuccessful in finding a solution via the internet. Unfortunately, I've spent hours trying and I'm really stuck. This seems uncomplicated. I am trying to run the code below.

Sub testDate()

Dim LYear As Date
Dim testDate As Date

LYear = Year(R2)
testDate = Date

Sheets("List").Range("R2").Formula = "= " & testDate & ""

Sheets("List").Range("S2").Formula = "=" & LYear & " "

End Sub

I get the following results.
R2 - 1/0/1900
S2 - 1/0/1900

R2 should be today's date and S2 should be only the year (2021).

R2 and S2 cells are formatted as Date (*3/14/2012).

Greatly appreciate assistance with this.

Karen

Dates are numbers that we see in date format thanks to cell formatting. For example, the date 6/28/2021 is the number 44375.
Operate in code on numbers (in this case on integers).

Please Login or Register  to view this content.

Cell S2 should be formatted as General.

Artik

Hi Artik,

Copied your code as written and changed S2 to General. Still not getting the result needed.
R2 results to 1/0/1900 and S2 results to 1899

Karen

Show in the attachment a sample workbook where it does not work.

Artik

Attached is the Excel sheet.

Thanks for taking the time to review.
Karen

It doesn't want to work because you didn't declare the variables, especially testDate must be a variable of the Long type. Without declaring, the variable takes the default Variant type. If a date is assigned to it (testDate = Date), the variable type automatically changes the subtype to Date. And that's where the problems begin.
1. Delete the contents of cells R2: S2.
2. Paste the code into the module in the same form as I gave it before.
3. Run the testDateRange macro twice.
After the second run, you should be fine.

Artik

Apologies. I should have caught that I hadn't declared the variables. I did as instructed and it works! THANKS

A couple of questions.
1. Why is it necessary to run the code twice before it works?
2. I was stuck on how to use declared variables in a formula. In your solution, the declared variable is not in the "" marks for the formula. Totally get this when the formula is simple like in my example. However, what if you want to use a variable in a more complex formula?

Example - The code below works great. But I want to replace Today() with the variable testDate. Tried various combinations based on what you provided with no success. I get the #NAME error in O2.

Sheets("List").Range("O2").Formula = "=If(AND(Today() >= List!$L$2, Today() <= List!$M$2), N2, False)"

L2 = 8/1/2020, M2 = 9/30/2020 (Both cells are formatted as Date - #3/14/2012)

Karen

Hi Artik,

Thanks! Works great. Really appreciate the time you have taken. I learned a lot.

I can see the difference when I ran the macro via Step Mode.

Karen