In your Google Sheet, Click “Tools” and then choose “Script Editor“. Put the code given below. Don’t forget to use function in your result column as “=dodiff(cell1,cell2)“
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | function dodiff(a, b) { if (a) a = fix(a); if (b) b = fix(b); if (Array.isArray(a) && Array.isArray(b)) { if (b[1] > a[1]) m = b[1] - a[1]; else { m = b[1] - a[1] + 60; b[0]--; } if (b[0] >= a[0]) h = b[0] - a[0]; else { h = b[0] - a[0] + 12; } m = m / 60; return (h + m).toPrecision(4); }}function fix(a) { if (a) x = a.toString().replace(" ", '').replace("am", "").replace("pm", "").replace("PM", "").replace("AM", ""); x = x.split(":"); x[0] = parseInt(x[0]); x[1] = parseInt(x[1]); return x;} |
Leave a Reply