Javascript function to convert Excel date format syntax (used in Google Apps Script spreadsheets) to simpleDateFormat syntax.

excelToSimpleDateFormat

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;
}