Skip to content

Instantly share code, notes, and snippets.

@jwbowles
Last active February 17, 2017 10:28
Show Gist options
  • Save jwbowles/7003ce2fcc2fb5661972 to your computer and use it in GitHub Desktop.
Save jwbowles/7003ce2fcc2fb5661972 to your computer and use it in GitHub Desktop.
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