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

User · Answer

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 :)

User · Answer

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 LongDouble 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

User · Answer

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

User · Answer

Public SUB test       Dim mdate As Date     mdate   now       MsgBox  Round CDbl mdate   0   End SUB

[excel] VBA Date as integer

Examples related to excel

Examples related to vba

Examples related to date

Examples related to datetime