Wednesday, February 2, 2011

yogi_Computing Time Worked From ??h??m Start and End Strings

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com





If Start Time is blank, End Time is blank or both are blank,
result needs to be blank

Formula in cell C2 ...
=ArrayFormula(if(len(A2:A)*len(B2:B),substitute(text((substitute(substitute(B2:B,"h",":"),"m","")+0)-(substitute(substitute(A2:A,"h",":"),"m","")+0)-"1:00","hh:mm"),":","h")&"m",iferror(1/0)))