We’re having an issue where an MS Excel file is generated by an external source and it’s our job to create a webMethods flow service to pick up the file, parse it, and put the data in a target system.
Everything is fine, except for the way that the dates are stored in the Excel file. They are stored as ‘sequential numbers’ or ‘serial values’.
As an example November 6th, 2006 01:23:45 is stored as ‘39027.0581597222’.
Does anyone know a way, in wM Developer, of converting the date from that decimal number to a more ‘readable’ format? We’re currently using Developer v6.1.
At this time, we’re unsure whether or not the date in the Excel file can be generated in a more standard way, but we figured we’d try all avenues first.
As a short follow up, we already have a service (incrementDate) that can properly add the correct number of days, but it’s mostly the time portion (the decimal part) that we’re having trouble with.
From the HELP file in MS Excel:
Any help or suggestions you guys 'n gals have would be appreciated.
So you’ve already developed service to figure out the date part and are looking into how to convert the decimal part into time. Here’s how I’d go about it (it might lead to some minor loss of precision):
10 places of decimal in excel = 1 day
ie 10000000000 (lets call it excel seconds) = 246060 secs = 86400 secs
1 excel second = 0.00000864 secs
Now take the decimal part (without the decimal of course) and multiply it by 0.00000864 to get actual seconds. Once you get the seconds, you can easily convert them into time.