Forum Discussion
Compute elapsed time
I have a spreadsheet of charging time for my Jeep. I would like to calculate the duration of the charging period but am having problems coming up with a formula that would work. It would have to...
If you enter the data as Date-Time combined format then a simple difference will work.
Here I basically combine the individual cells and then use HSTACK to then break Hour and Min up:
=LET(t,D5+TIME(E5,F5,0)-A5-TIME(B5,C5,0),HSTACK(HOUR(t),MINUTE(t)))
but each of those could be arrays too so all the results could be:
=LET(t,D5:D6+TIME(E5:E6,F5:F6,0)-A5:A6-TIME(B5:B6,C5:C6,0),HSTACK(HOUR(t),MINUTE(t)))
or more generally:
=LET(t,D5:.D99+TIME(E5:.E99,F5:.F99,0)-A5:.A99-TIME(B5:.B99,C5:.C99,0),HSTACK(HOUR(t),MINUTE(t)))