Subtracting/Adding dd:hh:mm:ss

[Lucy]

I am receiving data from several counters on field generators. The time is in
dd:hh:mm:ss. I am needing to calculate the run time on the equipment from one
day to the next and then sum the total time run for the week. I have created
a custom format for dd:hh:mm:ss for each reading but the subtraction (for
calculating the daily run time) or addition (sum of daily run times) formulas
are not working. Help please!

 

[Bernard Liengme]

Can you expand on "not working"
best wishes

 

[Lucy]

I am receiving a #Value error. If I walk through the calculation it tells me
that the cells currently being evaluated contain a constant.

 

[Fred Smith]

And show us the formulas you are using!

Regards,
Fred

 

[Bernard Liengme]

Are the entries true time values?
Suppose one of these times is in A10.
In some blank cell type =A10+TIME(1,0,0)
Does this formula return a time that is 1 hour later than the value in A10?
best wishes

 

[Dave Peterson]

Those values aren't really numbers (dates and times). They just look like it to
the human eye.

Try
=isnumber(a1)
and you'll see False.

You could use a helper column and a formula like:
=LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,255))
to convert it to a real number.

If the number of days is 31 or less, you could format it as:
dd hh:mm:ss

Or just leave it as a general format that'll show something like:
52.3496759259259

then you can use this helper column in your calculations--and format the results
the way you like.

(Even hide those helper columns???)

 

[Lucy]

Dave - I believe your formula did the trick:

03:12:24:58 03:12:24:59 03:12:24:38 03:12:24:24
03:21:25:38 03:21:25:39 03:21:25:40 03:21:25:41

84:24:58 84:24:59 84:24:38 84:24:24
93:25:38 93:25:39 93:25:40 93:25:41
9:00:40 9:00:40 9:01:02 9:01:17 01:12:03:39

Where the data entered is at the top, and then in my "helper" cell I have
your formula,
=LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,255)), then I
simply have =Sum (B2-B1) for the four columns, with a total =Sum(B1:B4) with
a dd:HH:mm:ss format applied.

My question to you, can you please explain this formula to me? Thanks so
much,
Lucy

 

[Dave Peterson]

First, in this formula:
=Sum(B2-B1)
you really don't need the =sum() portion.
=b2-b1
is sufficient.

Second, this portion of the formula:

=SEARCH(":",A1)
gives the position of the first colon in A1.
So
=search(":",a1)-1
is one position before that first colon (the last position of the number of
days)

=left(a1,search(":",a1)-1)
returns the number of days.

=mid(a1,search(":",a1)+1,255)
takes the text starting after the first colon for the next 255 characters
(you're using way less!).

=timevalue(mid(a1,search(":",a1)+1,255)
looks at that string and returns a real time (instead of a string).

Then it just adds the two (day and time) together.

Dave - I believe your formula did the trick:

03:12:24:58 03:12:24:59 03:12:24:38 03:12:24:24
03:21:25:38 03:21:25:39 03:21:25:40 03:21:25:41

84:24:58 84:24:59 84:24:38 84:24:24
93:25:38 93:25:39 93:25:40 93:25:41
9:00:40 9:00:40 9:01:02 9:01:17 01:12:03:39

Where the data entered is at the top, and then in my "helper" cell I have
your formula,
=LEFT(A1,SEARCH(":",A1)-1)+TIMEVALUE(MID(A1,SEARCH(":",A1)+1,255)), then I
simply have =Sum (B2-B1) for the four columns, with a total =Sum(B1:B4) with
a dd:HH:mm:ss format applied.

My question to you, can you please explain this formula to me? Thanks so
much,
Lucy

 

[Lucy]

Thank You, Thank You!!!!
--
Lucy

First, in this formula:
=Sum(B2-B1)
you really don't need the =sum() portion.
=b2-b1
is sufficient.

Second, this portion of the formula:

=SEARCH(":",A1)
gives the position of the first colon in A1.
So
=search(":",a1)-1
is one position before that first colon (the last position of the number of
days)

=left(a1,search(":",a1)-1)
returns the number of days.

=mid(a1,search(":",a1)+1,255)
takes the text starting after the first colon for the next 255 characters
(you're using way less!).

=timevalue(mid(a1,search(":",a1)+1,255)
looks at that string and returns a real time (instead of a string).

Then it just adds the two (day and time) together.