Days and hours

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
OK, here's what I got:

A1 Start date
A2 End date
A3 Start time
A4 End time

=INT(A3-A1) & ":" & TEXT(A4-A2+(A4<A2),"hh:mm")

Here's what I need,
I have this repeated for x number of cells. How do I keep a total? i. e. if one cell equals 8:7:24 and another cell equals 2:9:16 then the total cell would equal 10:16:46.
Thanks for the help
 
Just add them up as normal, say =B1+B2, but make sure the target cell is
formatted for time [h]:mm:ss to cater for more than 24 hours

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Clayton L. Wilson said:
Hi all,
OK, here's what I got:

A1 Start date
A2 End date
A3 Start time
A4 End time

=INT(A3-A1) & ":" & TEXT(A4-A2+(A4<A2),"hh:mm")

Here's what I need,
I have this repeated for x number of cells. How do I keep a total? i. e.
Click to expand...
if one cell equals 8:7:24 and another cell equals 2:9:16 then the total cell
would equal 10:16:46.
 
Hi Clayton
I would do this with using a helper column to store your individual
totals as number.
E.g. enter
=(A2-A1) + (A4-A3)
if you like format this with the custom format [hh]:mm

sum these helper cells. For your output you may use the following
formula (assumption the numeric value is stored in B1)
=INT(B1) & ":" & TEXT(B1-INT(B1),"hh:mm")
 
Back
Top