# Excel date number

I have this Excel date number: 42427,58403. I know it is ‘27-02-2016’ (Dutch locale) but how does Excel convert this to a date? I need to covert this number in Xojo.

Thanks

Check this out:

https://forum.xojo.com/24108-convert-a-vb-date

[quote=250531:@Jeff Tullin]Check this out:

https://forum.xojo.com/24108-convert-a-vb-date[/quote]

Mmm. I have a strange bug. I found your code:

```Dim d As New Date(1899,12,31) d.day = d.day + 42195.4519560185217415```

My code:

``````  dim theDate as new Date(1899,12,31)
theDate.day = theDate.day + calcDays -1``````

Calcdays = 42427,584027777775

This gives “22-9-1836”???

If you use MBS XL Plugin for excel files we have a function UnpackDate to convert between number and date.

Be aware that there may be more than one base date to consider, so this function checks what base date the current workbook has.

Hi Alexander,

Try this code to create the date:

[code] Dim ExcelTime as Double
ExcelTime = 42427.58403
Dim d as New Date(1900,1,1)

If ExcelTime > 32737 then
ExcelTime = ExcelTime - 32737
d = New Date(1989,8,17)
End If
d.Day = d.Day + ExcelTime
Label1.Text = Cstr(d.Year) +"/" + CStr(d.Month) + “/” + CStr(d.Day)[/code]

If the number is over 32737 then the new start date in Excel is 17 Aug 1989. If the Excel date is below 32737 then the starting date is 1 Jan 1900. The above code should show 2016/2/27 in Label1.

Exactly.

Hi all,

thanks for your replies. I found out that I could obtain the correct date by adding an extra integer.
Eugene, I will try your solution this evening.

Thanks

Hi Alexander,
This code gives exact the same date/time as in MS Excel:

``` Dim d As New date(1900,1,1,0,0,0) //date and time at start of the day TextField2.Text = "42427.58403" Dim da As Double = Val(TextField2.Text) -2 //count starts with 1 and MS Excel counts 1900 as a leapyear(which isn't true) Dim secs As Double = 24*60*60*da d.TotalSeconds = d.TotalSeconds+secs Label2.Text = d.SQLDateTime ```