Date formatting/conversion in transform map

[kjmauriello-999]

I am receiving xlsx files with dates that look like this 43364.13333 which should translate to this 2018-09-21 03:12:00 UTC.

I am having issues finding a way to convert the value to a date/time in ServiceNow.

I don't see any GlideDateTime functions that will do the conversion and have not been able to find any javascript functions either

Does anyone know the formula to convert 43364.13333 to this 2018-09-21 03:12:00, ServiceNow or Javascript?

Any assistance would be greatly appreciated.

[Allen Andreas]

Hi,

Are you able to let us know what date/time format that is for what's in your Excel document.

Does that format type have a name? That would help me at least look things up and see about a conversion.

Outside of that, in the transform map, when setting a date/time field, you're given a formatter that you'd want to use to tell the map what format your date/time is in.

Normally, though, this is to tell ServiceNow that your data is YYYY.MM.DD or MM/DD/YYYY, etc. and then ServiceNow takes care of the conversion for you.

With the format you're mentioning, I'm not sure what that is.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[kjmauriello-999]

Apparently the number to the left of the decimal is the date and the number to the right is the time.  In Excel you can set the cell format to custom.  Excel is able to take the value and convert it to date/time.  I'm looking for the formula they are using for the conversion

[-O-]

Perhaps a function like:

function getNumericValueOfExcelDateTimeSerial (dateTimeSerial) {
	var serial = +dateTimeSerial,
		daysPart = Math.floor(serial),
		timePart = serial % 1,
		hoursContained = timePart * 24,
		hours = Math.floor(hoursContained),
		minutesRemaining = hoursContained - hours,
		minutesContained = minutesRemaining * 60,
		minutes = Math.floor(minutesContained),
		secondsRemaining = minutesContained - minutes,
		secondsContained = secondsRemaining * 60,
		seconds = Math.floor(secondsContained);

	return Date.UTC(0, 0, daysPart - 1, hours, minutes, seconds);
}

It returns the numeric value of the date that can be used to set the numeric value of a GlideDateTime:

var nv = getNumericValueOfExcelDateTimeSerial(43364.13333);

gs.debug('nv:        ' + nv);

var gdt = new GlideDateTime();

gdt.setNumericValue(nv);

gs.debug('gdt (UTC): ' + gdt.toString());
gs.debug('gdt:       ' + gdt.getDisplayValueInternal());

If combined and executed in Scripts - Background, the output is:

*** Script: [DEBUG] nv:        1537499519000
*** Script: [DEBUG] gdt (UTC): 2018-09-21 03:11:59
*** Script: [DEBUG] gdt:       2018-09-21 06:11:59

My TZ is UTC+3.

Or the same could be used to initialize a JavaScript Date:

var nv = getNumericValueOfExcelDateTimeSerial(43364.13333);

gs.debug('nv:        ' + nv);

var gdt = new Date(nv);

gs.debug('gdt (UTC): ' + gdt.toUTCString());

The output is:

*** Script: [DEBUG] nv:        1537499519000
*** Script: [DEBUG] gdt (UTC): Fri, 21 Sep 2018 03:11:59 GMT

Should be thoroughly tested :D.

*) Inspired by: Converting Excel Date Serial Number to Date using JavaScript in All timezone.

[Community Alums]

Go to the import set table and check the datatype of the column. If it is string please change to date format.