Dates & Times
Implicit Date Operations
When working with Dates in Excel it is important to know the differences between VBA and .NET
In VBA you can use dates and times as "real" dates and times
#1/4/2007#
or you can use them as date serial numbers
36282.5
Explicit Date Operations
In .NET you will have to convert these dates explicitely.
There are two different ways of doing these conversions
Dim dbMyDate As Double
dbDate = Now().ToOADate
dbDate = System.DateTime.ToOADate(Now())
It is a similar story for dates that are supplied as strings
You should use the Parse method to convert a string to a date
Dim dbMyDate As Double
dbMyDate = System.DateTime.Parse("01 Jan 2008")
In VBA dates are stored internally as a serial value ( the number of days since Dec 30 1899)
in VBA times are stored as fractions.
In VBA dates are stored as a floating-point number and are represented as Doubles.
In VB.NET dates and times are stored completely differently.
In .NET a date maps to a System.DateTime
Converting VBA Doubles into .NET DateTimes
To convert between a VBA Double data type and a .NET DateTime data type you can use the following:
You should use the DateTime's ToOADate and FromOADate methods.
Dim myDate As DateTime
myDate = Date.FromOaDate(Globals.Sheet1.Range("A1").Value2
Microsoft.VisualBasic.DateTime.Day
VBA.Date
The DATE function returns the current system date.
VBA.Date
??
VBA.DateAdd
The DATEADD function returns the date to which a specified time interval has been added.
VBA.DateAdd
??
Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Date, _
Number, _
DateValue)
Microsoft.VisualBasic.DateAndTime.DateAdd(Interval, _
Number, _
DateValue)
VBA.DateDiff
The DATEDIFF function returns the number of time intervals between two specific dates.
VBA.DateDiff
??
Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Date, _
Date1, _
Date2, _
FirstDayOfWeek, _
FirstWeekOfYear)
Microsoft.VisualBasic.DateAndTime.DateDiff(Interval, _
Date1, _
Date2, _
FirstDayOfWeek, _
FirstWeekOfYear)
VBA.DatePart
The DATEPART function returns the specified part of a given date.
This method can now take an enumerated constant as well as a string constant.
VBA.DatePart
??
Microsoft.VisualBasic.DateAndTime.DatePart(Microsoft.VisualBasic.DateInterval.Date, _
Date, _
FirstDayOfWeek, _
FirstWeekOfYear)
Microsoft.VisualBasic.DateAndTime.DatePart(Interval, _
Date, _
FirstDayOfWeek, _
FirstWeekOfYear)
VBA.DateSerial
The DATESERIAL function returns the date given a year, month and day.
VBA.Format(VBA.DateSerial(2010,12,1), "dd-mmm")
new System.DateTime(2010,12,1).ToString("dd-mmm")
VBA.DateValue
The DATEVALUE function returns the date given a string representation of a date.
VBA.Format(VBA.DateValue(2010,12,1), "dd-mmm")
System.Convert.ToDateTime(2010,12,1).ToString("dd-mmm")
VBA.Day
The DAY function returns the day from a given date.
VBA.Day
??
VBA.Format
The FORMAT function returns the text string of a number or date in a particular format.
VBA.Format(VBA.Now(), "hh:mm:ss AMPM") = 10:40:17 AM
VBA.Format("12345,"#,###.00") = 12,345.00
VBA.Format("12345,"0.00") = 12345.00
System.DateTime.Now().ToString("hh:MM:ss tt")
System.Convert.ToInt32("12345").ToString("#,###.00")
System.Convert.ToInt32("12345").ToString("0.00")
VBA.Hour
The HOUR function returns the hour from a given time.
VBA.Hour
??
VBA.IsDate
The ISDATE function returns the value indicating if an expression is a date.
If (VBA.IsDate("01/12/2014") = True) Then
System.DateTime temp;
if (System.DateTime.TryParse("01/12/2014", out temp))
VBA.Now
The NOW function returns the current system date and time.
VBA.Now
System.DateTime.Now.ToString
VBA.Minute
The MINUTE function returns the minute from a given time.
VBA.Minute(VBA.Time)
System.DateTime.Now.Minute.ToString
VBA.Month
The MONTH function returns the month from a given date.
VBA.Month
VBA.MonthName
The MONTHNAME function returns the month as a string.
VBA.MonthName(3)
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(3)
Microsoft.VisualBasic.DateAndTime.MonthName(Month, _
Abbreviate)
VBA.Second
The SECOND function returns the second from a given time.
VBA.Second
VBA.Time
The TIME function returns the current system time.
VBA.Time
VBA.TimeSerial
The TIMESERIAL function returns the time for a specific hour, minute and second.
VBA.TimeSerial
VBA.TimeValue
The TIMEVALUE function returns the
VBA.TimeValue
VBA.Weekday
The WEEKDAY function returns the
VBA.Weekday("01/12/2010") = 4
System.Convert.ToInt32(System.Convert.ToDateTime("01/12/2014").DayOfWeek).ToString();
Microsoft.VisualBasic.DateAndTime.WeekdayName("01/12/2014")
VBA.WeekdayName
The WEEKDAYNAME function returns the day of the week as a string.
VBA.WeekdayName(2) = Tuesday
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.DayNames(2).ToString();
Microsoft.VisualBasic.DateAndTime.WeekdayName(2)
VBA.Year
The YEAR function returns the year from a given time.
VBA.Year
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext