Excel date number

General
1

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

2

Check this out:

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

3

[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”???

4

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

http://www.monkeybreadsoftware.net/xl-xlbookmbs-method1.shtml#21

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

5

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.

6

Exactly.

7

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

8

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