Times from Excel

[#1]

Hey, when I pull in a time from excel I get a number like  `-126167045`.  So when I try to add it to date, it gives a very weird number.  

I can get around this by turning it to a string and back into a number, but that seems weird.  Anyone know what's going on and if there's a better way than

:Date + Num( Format( :Time, "h:m:s" ) )

[#2]

Hi @vince_faller ,

  A few questions so that I can try and recreate the issue you're having:

1. Do you have some time data you can share?

2. When you pull in the data, are you using JSL to Open() the excel file and bring it in?

3. Or are you using the JMP add-in for excel to generate the data table from excel?

4. What is the time format in excel (h:m:s?, h:m?, etc.) and how is it formatted in excel (ctrl+1)?

5. What is you're operating system's time settings? I've come across problems where importing European number formats (comma as a decimal separator), JMP will treat it as nominal and not as a number. Changing the system settings can get around this -- or vice versa cause issues.

Thanks!,

DS

[#3]

Names default to here(1);
New Table("Example",
	<< New Column("Date", <<Format("m/d/y"), <<Set Values({Num("2024-02-16")})),
	<< New Column("Time", <<Format("h:m:s"), << Set Values({-126167045})), 
	<< New Column("DateTime", <<Format("m/d/y h:m:s"), << Set Formula(:Date + :Time))
)

Just pulling in the attached data via. 

dt = open("$DESKTOP/thiny.xlsx");
show(dt:Time[1])

[#4]

I think that isn't really a time but rather date with time (not sure why JMP is pulling it in like that though and not adjusting it). And other issue is that for whatever reason the time is negative even if you use Time Of Day function (most likely just because Excel's date starts from 1.1.1900 and JMPs from 1.11904).

You could perform the conversion like this (Time3 column)

Names Default To Here(1);

dt = Open("$DOWNLOADS/thiny.xlsx");
Column(dt, "Time") << Format("Format Pattern", "<YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>", 19, 0);

// In Hours(16) + In Minutes(34) + 31;
// -26729.2820000052? negative value for time of day
dt << New Column("Time2", Numeric, Continuous, Format("h:m:s", 11, 0), Formula(
	Time Of Day(:Time) // negative?
));

dt << New Column("Time3", Numeric, Continuous, Format("h:m:s", 11, 0), Formula(
	If(:Time < 1,
		In Hours(24) + Time Of Day(:Time)
	,
		Time Of Day(:Time)
	);
));

dt << New Column("DateTime", Numeric, Continuous,
	Format("Format Pattern", "<YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>", 19, 0),
	Formula(:Date + :Time3)
);

First check if time of day is negative and if it is, then deduct it from the seconds in single day to get "correct" time for dates after 1.1.1904.

This might be necessary but I'm not sure how to feel about this

New Table("Sheet1 2",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Date",
		Numeric,
		"Continuous",
		Format("Format Pattern", "<D> <MMM> <YYYY>", 12),
		Input Format("Format Pattern", "<D> <MMM> <YYYY>"),
		Set Values([3790540800])
	),
	New Column("Time",
		Numeric,
		"Continuous",
		Format("Format Pattern", "<YYYY><-><MM><-><DD><'T'><hh24><::><mm><::><ss>", 19, 0),
		Input Format("Format Pattern", "<hh><:><mm><:><ss> <ampm>", 0),
		Set Values([-126170729.282]),
		Set Display Width(146)
	),
	New Column("Time2Time", Numeric, "Continuous", Format("h:m:s", 12, 0), Input Format("h:m:s", 0), Set Values([-26729.2820000052])),
	New Column("Time2Best", Numeric, "Continuous", Format("Best", 11), Formula(Time Of Day(:Time))),
	New Column("Time3Time", Numeric, "Continuous", Format("h:m:s", 11, 0), Input Format("h:m:s", 0), Set Values([59670.7179999948])),
	New Column("Time3Best", Numeric, "Continuous", Format("Best", 11), Formula(If(:Time < 1, In Hours(24) + Time Of Day(:Time), Time Of Day(:Time))))
)

[#5]

My workaround of 

:Date + Num( Format( :Time, "h:m:s" ) )

 Works.