Value of Date Variable Changing When Being Populated To A Range On Another Worksheet

[Ark68]

I am experiencing a very frustrating problem that I hope someone can offer some help with.
Consider this code in a standard module.

    tdate = Now - 1
    tyear = Year(tdate)
    tleap = Application.WorksheetFunction.VLookup(tyear, ws_lists.Range("AG2:AH9"), 2, False)
    tmonth = Month(tdate)
    tday = Day(tdate)
    mbevents = False
    With ws_gui
        .Unprotect
        .Range("B4") = ""
        .Range("B4") .value= tdate
        .Protect
    End With

The main goal is to put the serial date value of "yesterday" in cell B4 of worksheet ws_gui.

As I step through the code
tdate = 2022-01-27 12:42:21 PM
tyear = 2022
tleap = False
tmonth = 1
tday = 27

Since today is January 28, 2022, these values are correct.

For testing, I put a null value in worksheet ws_gui.range("B4"). The previous value is eliminated so I can assume that the code is reflected on the correct sheet.
However, when I populate worksheet ws_gui.range("B4") with the value for tdate, I get a serial of 44589 ... which is January 28, 2022. How did tdate increase by one day? tdate was 2022-01-27.

Note that this code is being run from a focused worksheet other than ws_gui in case than matters

 

[Marc L]

​

What a mess to guess without the complete VBA procedure !​

​

Anyway if the tdate variable is declared as Date so just obviously try tdate = Date - 1 …​

Check also the cell format.​

​