C# Excel Interop - Dates & Times

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 datatype and a .NET DateTime datatype 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