Dates & Times
VBA.Date
The DATE function returns the current system date.
'VBA Code
VBA.Date
'C# Equivalent
VBA.DateAdd
The DATEADD function returns the date to which a specified time interval has been added.
'VBA Code
VBA.DateAdd
Microsoft.VisualBasic.DateAndTime.DateAdd(Microsoft.VisualBasic.DateInterval.Date, _
Number, _
DateValue)
Microsoft.VisualBasic.DateAndTime.DateAdd(Interval, _
Number, _
DateValue)
'C# Equivalent
VBA.DateDiff
The DATEDIFF function returns the number of time intervals between two specific dates.
'VBA Code
VBA.DateDiff
Microsoft.VisualBasic.DateAndTime.DateDiff(Microsoft.VisualBasic.DateInterval.Date, _
Date1, _
Date2, _
FirstDayOfWeek, _
FirstWeekOfYear)
Microsoft.VisualBasic.DateAndTime.DateDiff(Interval, _
Date1, _
Date2, _
FirstDayOfWeek, _
FirstWeekOfYear)
'C# Equivalent
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 Code
VBA.DatePart
Microsoft.VisualBasic.DateAndTime.DatePart(Microsoft.VisualBasic.DateInterval.Date, _
Date, _
FirstDayOfWeek, _
FirstWeekOfYear)
Microsoft.VisualBasic.DateAndTime.DatePart(Interval, _
Date, _
FirstDayOfWeek, _
FirstWeekOfYear)
'C# Equivalent
VBA.DateSerial
The DATESERIAL function returns the date given a year, month and day.
'VBA Code
VBA.Format(VBA.DateSerial(2010,12,1), "dd-mmm")
'C# Equivalent
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 Code
VBA.Format(VBA.DateValue(2010,12,1), "dd-mmm")
'C# Equivalent
System.Convert.ToDateTime(2010,12,1).ToString("dd-mmm")
VBA.Day
The DAY function returns the day from a given date.
'VBA Code
VBA.Day
Microsoft.VisualBasic.DateTime.Day
'C# Equivalent
??
VBA.Format
The FORMAT function returns the text string of a number or date in a particular format.
'VBA Code
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
'C# Equivalent
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 Code
VBA.Hour
'C# Equivalent
??
VBA.IsDate
The ISDATE function returns the value indicating if an expression is a date.
'VBA Code
If (VBA.IsDate("01/12/2014") = True) Then
'C# Equivalent
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 Code
VBA.Now
'C# Equivalent
System.DateTime.Now
VBA.Minute
The MINUTE function returns the minute from a given time.
'VBA Code
VBA.Minute(VBA.Time)
'C# Equivalent
System.DateTime.Now.Minute.ToString
VBA.Month
The MONTH function returns the month from a given date.
'VBA Code
VBA.Month
'C# Equivalent
??
VBA.MonthName
The MONTHNAME function returns the month as a string.
'VBA Code
VBA.MonthName(3)
Microsoft.VisualBasic.DateAndTime.MonthName(Month, Abbreviate)
'C# Equivalent
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(3)
VBA.Second
The SECOND function returns the second from a given time.
'VBA Code
VBA.Second
'C# Equivalent
VBA.Time
The TIME function returns the current system time.
'VBA Code
VBA.Time
'C# Equivalent
??
VBA.TimeSerial
The TIMESERIAL function returns the time for a specific hour, minute and second.
'VBA Code
VBA.TimeSerial
'C# Equivalent
VBA.TimeValue
The TIMEVALUE function returns the
'VBA Code
VBA.TimeValue
'C# Equivalent
??
VBA.Weekday
The WEEKDAY function returns the
'VBA Code
VBA.Weekday("01/12/2010") = 4
Microsoft.VisualBasic.DateAndTime.WeekdayName("01/12/2014")
'C# Equivalent
System.Convert.ToInt32(System.Convert.ToDateTime("01/12/2014").DayOfWeek).ToString();
VBA.WeekdayName
The WEEKDAYNAME function returns the day of the week as a string.
'VBA Code
VBA.WeekdayName(2) = Tuesday
Microsoft.VisualBasic.DateAndTime.WeekdayName(2)
'C# Equivalent
System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.DayNames(2).ToString();
VBA.Year
The YEAR function returns the year from a given time.
'VBA Code
VBA.Year
'C# Equivalent
System.DateTime.Year
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
Dates and DateTime
Range["A1"].Value = "08/11/04"
This value could be interpreted as any of the following dates depending on the locale of the current thread
August 11, 2004
November 8, 2004
November 4, 2008
The workaround is to never pass dates as literal strings into Excel.
Always construct a date using the System.DateTime object and pass it to Excel using the DateTime.ToOADate method
The ToOADate method converts a DateTime to an OLE Automation date which is the date format used by Excel.
System.DateTime odate = new System.DateTime(2014,4,11);
range1.Value = odate.ToOADate();
Excel calculates the number of days elapsed since 31 December 1899
.NET calculates the number of days elapsed since 1 January 1900
To convert an Excel date in 1900 that is earlier than 1 March 1900 into a DateTime object you must add one day
Excel cannot represent days before 1 January 1900 or (I January 1904) so when converting a DateTime into an Excel date, you have to pass a string rather than a number representing the date.
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext