jlemesur2112
asked on
How can I covert a serial date to a different format in JavaScript?
I am exporting an excel spreadsheet to xml and using it in a webpage.
Excel converts the dates to a serial date. This is okay for sorting the data sets but I need to display that date as well. I need a function that will convert a serial date (i.e 40452) to this format: MM/dd/yyyy
Excel converts the dates to a serial date. This is okay for sorting the data sets but I need to display that date as well. I need a function that will convert a serial date (i.e 40452) to this format: MM/dd/yyyy
=TEXT(EDATE([cell],1),"MM/ dd/yyyy"
or just =EDATE([cell],1)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Expanding a bit upon Proculopsis' suggestion:
function z(x){var w="00"+x;return w.substr(w.length-2);}
var d = new Date( "1/1/1900" );
d.setDate( d.getDate() + 40452 );
alert( z((d.getMonth()+1)) + "/" + z(d.getDate()) + "/" + d.getFullYear() );
And please, no points for me, thanks.
ASKER
Proculopsis,
This looks good but I have to ask what would be the best way to add the serial date from a Spry value.
i.e. d.setDate( d.getDate() + '{@spryDateValue}' );
{@spryDateValue} is the serial date for the XML files
Would this work?
var d = new Date( "1/1/1900" );
var d2 = new Date( "{@spryValue}" );
d.setDate( d.getDate() + d2.getDate() );
This looks good but I have to ask what would be the best way to add the serial date from a Spry value.
i.e. d.setDate( d.getDate() + '{@spryDateValue}' );
{@spryDateValue} is the serial date for the XML files
Would this work?
var d = new Date( "1/1/1900" );
var d2 = new Date( "{@spryValue}" );
d.setDate( d.getDate() + d2.getDate() );
I'm not sure of your xml format but this example retrieves a serial date via a transformation and jQuery manipulation:
<html>
<head>
<title>http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_26665644.html</title>
<script language="javascript" src="http://code.jquery.com/jquery-1.4.3.min.js"></script>
<script>
var xmlSource = "\
<?xml version='1.0' encoding='UTF-8'?> \
<query> \
<clients> \
<client> \
<Listeddate>40452</Listeddate> \
<amount>0</amount> \
<paid>5200</paid> \
<paymentdate>40471</paymentdate> \
</client> \
</clients> \
</query>";
xslSource = "<?xml version='1.0' encoding='ISO-8859-1'?>\
<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>\
<xsl:template match='/'>\
<table border='1'>\
<tr bgcolor='#9acd32'>\
<th align='left'>Listed</th>\
<th align='left'>Amount</th>\
<th align='left'>Paid</th>\
<th align='left'>Date</th>\
</tr>\
<xsl:for-each select='query/clients/client'>\
<tr>\
<td class='serial-date'><xsl:value-of select='Listeddate' /></td>\
<td><xsl:value-of select='amount' /></td>\
<td><xsl:value-of select='paid' /></td>\
<td class='serial-date'><xsl:value-of select='paymentdate' /></td>\
</tr>\
</xsl:for-each>\
</table>\
</xsl:template>\
</xsl:stylesheet>";
function parser( xml ) {
var result = new ActiveXObject( "Microsoft.XMLDOM" );
result.async = false;
result.loadXML( xml );
return result;
}
$( function() {
var xml = parser( xmlSource );
var xsl = parser( xslSource );
var transform = xml.transformNode(xsl);
$("#example").html( transform );
$(".serial-date").each( function() {
$(this).html( serialDate( $(this).text() ) );
});
});
function serialDate( serial ) {
var d = new Date( "1/1/1900" );
d.setDate( serial );
return (d.getMonth()+1) + "/" + d.getDate() + "/" + d.getFullYear();
}
</script>
</head>
<body>
<div id="example" />
</body>
</html>