Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- =ARRAYFORMULA(IF(LEN(A55:A), REGEXREPLACE(
- IFERROR(REGEXEXTRACT(SUBSTITUTE(A55:A, "h", ":"), "(d+):"), 0)+
- IFERROR(QUOTIENT(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(d+)m"), 60))&"h "&
- IFERROR(MOD(REGEXEXTRACT(TO_TEXT(A55:A)&"m", "(d+)m"), 60)&"m", "0m"), "^0h ", ), ))
- function onEdit(e){
- }
- function onEdit(e) {
- var duration = stringToDuration(e.value);
- if(e.value !== duration) {
- e.range.setValue(duration);
- e.range.setNumberFormat('[h]:mm');
- }
- }
- /**
- * Converts a string to a Google Sheet support duration value
- *
- * @param {String} input The string to be converted
- * @returns An Integer if the input format is supported otherwise returns the input
- */
- function stringToDuration(input){
- if(!typeof input === 'string') return input;
- var date = new Date(0);
- var match = input.match(/^(d.*)(.)/);
- var value = parseFloat(match[1]);
- var isDuration = true;
- switch(input[2]){
- case 'm':
- date.setMinutes(value);
- break;
- case 'h':
- date.setHours(value);
- break;
- default:
- output = input;
- isDuration = false;
- }
- var output;
- if(isDuration) {
- var oneDayInMS = 24*60*60*1000;
- output = date.getTime() / oneDayInMS;
- } else {
- output = input;
- }
- return output
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement