VBA Date as integer

Question

is there a way to get the underlying integer for Date function in VBA ? I'm referring to the integer stored by Excel to describe dates in memory in terms of number of days (when time is included it can be a float then I guess). I'm only interest in the integer part though. Is there just another function for that ?

For example, for today() I'd like to be able to get back 40877..

Tomalak · Accepted Answer · 2020-05-27T16:25:33.820

37

Date is not an Integer in VB(A), it is a Double.

You can get a Date's value by passing it to CDbl().

CDbl(Now())      ' 40877.8052662037

From the documentation:

The 1900 Date System

In the 1900 date system, the first day that is supported is January 1, 1900. When you enter a date, the date is converted into a serial number that represents the number of elapsed days starting with 1 for January 1, 1900. For example, if you enter July 5, 1998, Excel converts the date to the serial number 35981.

So in the 1900 system, 40877.805... represents 40,876 days after January 1, 1900 (29 November 2011), and ~80.5% of one day (~19:19h). There is a setting for 1904-based system in Excel, numbers will be off when this is in use (that's a per-workbook setting).

To get the integer part, use

Int(CDbl(Now())) ' 40877

which would return a ~~Long~~Double with no decimal places (i.e. what Floor() would do in other languages).

Using CLng() or Round() would result in rounding, which will return a "day in the future" when called after 12:00 noon, so don't do that.

edited May 27 '20 at 16:25

answered Nov 30 '11 at 18:22

Tomalak

306,836
62
485
598

Int(CDbl(Now())) does not return a long, it returns a double. The double's value just happens to represent an integer, but it's data type is still double. – phoog Nov 30 '11 at 19:22
@phoog: `TypeName(VarType(Int(CDbl(Now())))) = "Long"` on my system – Tomalak Nov 30 '11 at 19:29
@Tomalek of course, because `VarType(Int(CDbl(Now())))` returns 5, which is the value of `vbDouble`, but is itself a `Long`. Try `TypeName(Int(CDbl(Now())))`. – phoog Nov 30 '11 at 19:34
2

If you use `Date` instead of `Now` all these part day problems go away - `Date` (the function not the data type) returns the day with no time component. – Jon Egerton Nov 30 '11 at 21:46
2

@JonEgerton not to mention that the OP was asking about the Date function in the first place, not the Now function. – phoog Nov 30 '11 at 22:00
@Tomalak This was a great answer - to remind us the format is double, not integer - a common misconception and how `Int()` is like `Math.floor` (JavaScript). Saved me, too! Thanks. – vapcguy Mar 30 '16 at 14:06

score 15 · Answer 2 · answered Nov 30 '11 at 18:20

15

Just use CLng(Date).

Note that you need to use Long not Integer for this as the value for the current date is > 32767

answered Nov 30 '11 at 18:20

Jon Egerton

36,729
11
90
125

Indeed I didnt think of the Long/Integer issue ! Thanks ! – BuZz Nov 30 '11 at 18:22
2

CLng will round afternoon values up to the next day. – phoog Nov 30 '11 at 19:23
@phoog. I've used `Date` not `Now`. `Date` has no time component, therefore `CLng` is fine and safe. – Jon Egerton Nov 30 '11 at 21:44
@JonEgerton of course. I overlooked that. I didn't cast the downvote, so I can't remove it :( I did add an upvote, however :) – phoog Nov 30 '11 at 21:45
@phoog: no worries, that makes me net +8! – Jon Egerton Nov 30 '11 at 21:50

IshaniNet · Answer 3 · 2017-06-04T08:30:23.897

You can use bellow code example for date string like mdate and Now() like toDay, you can also calculate deference between both date like Aging

Public Sub test(mdate As String)
    Dim toDay As String
    mdate = Round(CDbl(CDate(mdate)), 0)
    toDay = Round(CDbl(Now()), 0)
    Dim Aging as String
    Aging = toDay - mdate
    MsgBox ("So aging is -" & Aging & vbCr & "from the date - " & _
    Format(mdate, "dd-mm-yyyy")) & " to " & Format(toDay, "dd-mm-yyyy"))
End Sub

NB: Used CDate for convert Date String to Valid Date

I am using this in Office 2007 :)

score 1 · Answer 4 · answered Nov 30 '11 at 18:22

1

Public SUB test()
    Dim mdate As Date
    mdate = now()
    MsgBox (Round(CDbl(mdate), 0))
End SUB

answered Nov 30 '11 at 18:22

xQbert

31,937
2
37
57

VBA Date as integer

4 Answers4

The 1900 Date System

Linked