Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Javascript function to convert Excel date format syntax (used in Google Apps Script spreadsheets) to simpleDateFormat syntax.
function excelToSimpleDateFormat(date, str) {
var excelNonReserved = /([^(m+|d+|y+|am\/pm|a\/p|h+|s+|0+|\[h+\]|\[m+\]|\[s+\])])/gi;
var excelElements = /("[^"]+"|m+|d+|y+|am\/pm|a\/p|h+|s+|0+|\[h+\]|\[m+\]|\[s+\])/gi;
var restrictedChars = /[a-z]+/gi;
var quotes = /"([^"]+)"/;
var minute = /m/g;
var month = /M/g;
var hour = /h/gi;
var seconds = /s/gi;
var ampm = /(a\/p|am\/pm)/gi;
var dayShort = /ddd/g;
var dayLong = /dddd+/g;
var millisecond = /0/g;
var elapsedHour = /\[h+\]/gi;
var elapsedMinute = /\[m+\]/gi;
var elapsedSecond = /\[s+\]/gi;
var base_date = new Date(1899,11,30);
var msInHour = 3600000;
var hoursElapsed=Math.floor((date-base_date)/msInHour);
var minutesElapsed=Math.floor((date-base_date)*60/msInHour);
var secondsElapsed=Math.floor((date-base_date)*3600/msInHour);
var simpleDateFormat = "";
if(str.length < 1){
str = "M/d/YYYY H:mm:ss (z)"; //Default Format
}
var dateFormat = str.replace(minute,"M");
var elements = dateFormat.split(excelElements);
for (var i = 0; i < elements.length; i++){
if(elements[i] == ""){ //clear empty indices
elements.splice(i,1);
}
}
for (var i = 0; i < elements.length; i++){
if(elements[i].match(quotes)){
elements[i] = elements[i].replace(/"/g,"");
elements[i] = elements[i].replace(restrictedChars, "");
}else if(elements[i].match(month)){
if(elements[i-1] != null && elements[i-1].match(excelNonReserved) && elements[i-2] != null && elements[i-2].match(hour)){
elements[i] = elements[i].replace(month,"m");
}else if(elements[i+1] != null && (elements[i+1].match(quotes) || elements[i+1].match(excelNonReserved)) && elements[i+2] != null && elements[i+2].match(seconds)){
elements[i] = elements[i].replace(month,"m");
}else if(elements[i-1] != null && elements[i-1].match(hour)){
elements[i] = elements[i].replace(month,"m");
}else if(elements[i+1] != null && elements[i+1].match(seconds)){
elements[i] = elements[i].replace(month,"m");
}
}
simpleDateFormat += elements[i];
}
simpleDateFormat = simpleDateFormat.replace(ampm, "a");
simpleDateFormat = simpleDateFormat.replace(dayLong, "EEEE");
simpleDateFormat = simpleDateFormat.replace(dayShort, "EEE");
simpleDateFormat = simpleDateFormat.replace(millisecond, "S");
simpleDateFormat = simpleDateFormat.replace(elapsedHour, hoursElapsed);
simpleDateFormat = simpleDateFormat.replace(elapsedMinute, minutesElapsed);
simpleDateFormat = simpleDateFormat.replace(elapsedSecond, secondsElapsed);
if(!str.match(ampm)){
simpleDateFormat = simpleDateFormat.replace(hour, "H");
}
return simpleDateFormat;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment