Advertisement
Guest User

Untitled

a guest
Jun 25th, 2019
78
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.24 KB | None | 0 0
  1. =ARRAYFORMULA(IF(LEN(A55:A), REGEXREPLACE(
  2. IFERROR(REGEXEXTRACT(SUBSTITUTE(A55:A, "h", ":"), "(d+):"), 0)+
  3. IFERROR(QUOTIENT(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(d+)m"), 60))&"h "&
  4. IFERROR(MOD(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(d+)m"), 60)&"m", "0m"), "^0h ", ), ))
  5.  
  6. function onEdit(e){
  7.  
  8. }
  9.  
  10. function onEdit(e) {
  11. var duration = stringToDuration(e.value);
  12. if(e.value !== duration) {
  13. e.range.setValue(duration);
  14. e.range.setNumberFormat('[h]:mm');
  15. }
  16. }
  17.  
  18. /**
  19. * Converts a string to a Google Sheet support duration value
  20. *
  21. * @param {String} input The string to be converted
  22. * @returns An Integer if the input format is supported otherwise returns the input
  23. */
  24. function stringToDuration(input){
  25. if(!typeof input === 'string') return input;
  26. var date = new Date(0);
  27. var match = input.match(/^(d.*)(.)/);
  28. var value = parseFloat(match[1]);
  29. var isDuration = true;
  30. switch(input[2]){
  31. case 'm':
  32. date.setMinutes(value);
  33. break;
  34. case 'h':
  35. date.setHours(value);
  36. break;
  37. default:
  38. output = input;
  39. isDuration = false;
  40. }
  41. var output;
  42. if(isDuration) {
  43. var oneDayInMS = 24*60*60*1000;
  44. output = date.getTime() / oneDayInMS;
  45. } else {
  46. output = input;
  47. }
  48. return output
  49. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement