Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@andreuinyu
Created January 14, 2017 10:47
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save andreuinyu/32bec70810ce266bad2421644053d279 to your computer and use it in GitHub Desktop.
Save andreuinyu/32bec70810ce266bad2421644053d279 to your computer and use it in GitHub Desktop.
Google Spreadsheet function that takes two strings in the format "January 1, 1970 at 00:00AM" and returns the time difference rounded into hours
function DELTAHOURS(date1, date2) {
//Takes two strings in the format "January 1, 1970 at 00:00AM" and returns the time delta in hours
var starthour = new Date();
var endhour = new Date();
//Month
switch (date1.slice(0, date1.indexOf(" "))){
case "January":
starthour.setMonth(0);
break;
case "February":
starthour.setMonth(1);
break;
case "March":
starthour.setMonth(2);
break;
case "April":
starthour.setMonth(3);
break;
case "May":
starthour.setMonth(4);
break;
case "June":
starthour.setMonth(5);
break;
case "July":
starthour.setMonth(6);
break;
case "August":
starthour.setMonth(7);
break;
case "September":
starthour.setMonth(8);
break;
case "October":
starthour.setMonth(8);
break;
case "November":
starthour.setMonth(10);
break;
case "December":
starthour.setMonth(11);
break;
}
switch (date2.slice(0, date2.indexOf(" "))){
case "January":
endhour.setMonth(0);
break;
case "February":
endhour.setMonth(1);
break;
case "March":
endhour.setMonth(2);
break;
case "April":
endhour.setMonth(3);
break;
case "May":
endhour.setMonth(4);
break;
case "June":
endhour.setMonth(5);
break;
case "July":
endhour.setMonth(6);
break;
case "August":
endhour.setMonth(7);
break;
case "September":
endhour.setMonth(8);
break;
case "October":
endhour.setMonth(8);
break;
case "November":
endhour.setMonth(10);
break;
case "December":
endhour.setMonth(11);
break;
}
//Day
starthour.setDate(Number(date1.slice(date1.indexOf(" "), date1.indexOf(","))));
endhour.setDate(Number(date2.slice(date2.indexOf(" "), date2.indexOf(","))));
//Year
starthour.setFullYear(Number(date1.slice(date1.indexOf(",")+2, date1.indexOf("at")-1)));
endhour.setFullYear(Number(date2.slice(date1.indexOf(",")+2, date2.indexOf("at")-1)));
//Hour and minute
var h1 = date1.slice(date1.indexOf("at")+3, date1.length-2);
var h2 = date2.slice(date2.indexOf("at")+3, date2.length-2);
switch (date1[date1.length-2]){
case "A":
starthour.setHours(Number(h1.slice(0, 2)));
break;
case "P":
starthour.setHours(Number(h1.slice(0, 2))+12);
}
switch (date2[date2.length-2]){
case "A":
endhour.setHours(Number(h2.slice(0, 2)));
break;
case "P":
endhour.setHours(Number(h2.slice(0, 2))+12);
}
starthour.setMinutes(Number(h1.slice(3, h1.length)));
endhour.setMinutes(Number(h2.slice(3, h2.length)));
return Math.round((endhour.getTime()-starthour.getTime())/(3600*1000));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment