Decimal time and date code from Excel to common format.

Hi!,

I'm trying to convert a decimal time&date number into the normal date format inside Grasshopper.

If someone has tried this before, please, contect me :)

Best Regards.

Replies to This Discussion

Permalink Reply by Giulio Piacentino on November 27, 2010 at 1:01pm

Hi Angel,

I don't know what is a decimal time&date - is it a time interval from a specific date?
Can you post a couple of examples of a decimal time&date with a resulting date formatted like 2010/11/27 12:00:00 UTC?

- Giulio
_______________
giulio@mcneel.com
McNeel Europe, Barcelona

Permalink Reply by Ángel Linares on November 27, 2010 at 1:03pm

Fix[(Number/365) + 1900] = Year.

One step forward :) I'm keeping working myself xD.

Is there no VB.NET function to convert it automaticallly?

Permalink Reply by Garcia del Castillo on November 27, 2010 at 4:28pm

Cool! So what's the common?

40150 = Year 2010 ?

Permalink Reply by Ángel Linares on November 27, 2010 at 4:50pm

It's the format that excel uses to calculate dates. When you export Excel data to GH (using Ghowl) data in Date Format cells is converted into a decimal time&date format.

In this system, the serial number 1 represents 1/1/1900 12:00:00 a.m. Times are stored as decimal numbers between .0 and .99999, where .0 is 00:00:00 and .99999 is 23:59:59. The date integers and time decimal fractions can be combined to create numbers that have a decimal and an integer portion. For example, the number 32331.06 represents the date and time 7/7/1988 1:26:24 a.m.

More info: http://support.microsoft.com/kb/214094

Permalink Reply by Garcia del Castillo on November 27, 2010 at 6:08pm

Jeje, according to that formula, I guess there is no way then to represent a Feb 29th... right?

(I should be thinking of other stuff on a Saturday night...)

Permalink Reply by Ángel Linares on November 28, 2010 at 5:20am

Excel uses this formula...I think that microsoft people has dealt with it to make it work properly (hope).

(Saturday is not a bad day to be thinking about that if you came some days ago from the other side of the world :P)

Permalink Reply by Ángel Linares on November 28, 2010 at 6:49am

I have finished a definition that make this calcs. I hope it helps somebody :)

Best.

Attachments:

Excel_date_to_Roman.ghx, 216 KB

Permalink Reply by Giulio Piacentino on November 28, 2010 at 11:18am

Hi again Angel,

I think that the .Net framework method DateTime.FromOADate(double timeValue) seems to do something in this direction and would work well in this case.

This is commented C# code that does something similar in the script. I also fear that there is 1 day difference from the implementation that is published (don't know why :) ).

//Retrieve the local culture as exposed in the Windows User Interface

System.Globalization.CultureInfo showAs = System.Globalization.CultureInfo.InstalledUICulture;



//Parse the date

DateTime dateValue = DateTime.FromOADate(x);



//Show the date as interchangeable data and in the local culture

date = dateValue;

Print(dateValue.ToString("F", showAs));

Also, one could use a one liner, if the additional info is not necessary:
A = DateTime.FromOADate(x);
Code also in Vb.Net. I hope this helps.

- Giulio
_______________
giulio@mcneel.com
McNeel Europe, Barcelona

Attachments:

Excel_date_in_cs.ghx, 244 KB

Permalink Reply by Ángel Linares on November 28, 2010 at 1:49pm

Hey, thanks! It helps a lot :)

Mmm...1 day diference is a mistery. I have cheked the definition with the excel source and the is no mistakes :S...

I'll try to make a VB componet and use DateTime.FromOADate function.

Best.

Permalink Reply by Ángel Linares on November 28, 2010 at 1:56pm

mmmm...I have checked this code and there is no diference between the result of my definition and the one that come back from the VB.NET code.

Anyway, VB.NET function is better than a big definition :)

P.S.: Use the XLS file to make comparisons.

Attachments:

Comparación.ghx, 299 KB
Público - Privado - Público & Privado.xls, 34 KB

Permalink Reply by Ángel Linares on November 28, 2010 at 2:33pm

Ok, this a the "complete" tool writed in VB.NET. It returns complete date&time, Day, Month, Hour, Minute and Second value from Excel serial Date&Time date linked with gHowl.

If someone needs Year data, he only needs to add and Output and write "Output = Dat.Year" to the code.

I hope this helps.

Best Regards.

Attachments: