Returning 5-digit date in Debug.Print without formatting the cell

Question

Background:

I am looking to get the 5-digit date from a cell which is formatted to display a date. The 5-digit date should only be the immediate window (via debug.print).

My testing with results in the Script section (bottom).

I have a feeling that the answer will involve datediff()+2 based on the testing I did, but I can't figure out why that +2 is needed and don't want to just add that in if it's wrong in the future.

---

Issue:

I don't seem to be able to display the correct 5-digit date (as displayed with "general" format) by means of Debug.Print.

---

Question:

How do you get the 5-digit date, most often displayed with General-formatting, to display in the immediate window (debug.print) without first converting the cell's .numberformat?

---

Script:

Dim rng As Range, d1 As String, d2 As String
Set rng = Cells(1, 1) 'value = 20190101

Debug.Print Format(rng.Value, "@") 'returns 20190101
Debug.Print rng.NumberFormat = "General" 'returns "false"
Debug.Print Format(rng.value, "General") 'returns "Ge0oral" due to the "n" being recognized for a format

d1 = "1900/01/01"
d2 = rng.Value
Debug.Print 40729 + DateDiff("d", d1, d2) 'returns 84195
Debug.Print DateDiff("d", d1, d2) 'returns 43466

rng.NumberFormat = "General"
Debug.Print rng.Value 'returns 43468, but required formatting the cell

Answer 1

Range.Value2 doesn't use the Date type, so you can just Debug.Print rng.Value2.

Answer 2

There are two methods that come to mind when you are wanting to print the numerical representation of the date without changing the formatting of the cell.

The best way to do it is using the .Value2 property.

Debug.Print Rng.Value2

The 'less recommended' approach is by using the CDbl() function. I would use this moreso for variables and .Value2 for ranges.

Debug.Print CDbl(Rng.Value)

Or for variables

Dim d as Date
d = #01/01/2019#

Debug.Print CDbl(d)
Debug.Print CLng(d) 'If not using time - date only

Answer 3

Nice layout Cyril =)

To complement the other answers: Whereas Value2 is the more appropriate way to go, another very simple possibility is:

Debug.Print rng*1