add/sub time/hour tracking in 24 clock format

[JsGuero]

I am trying to write a formula to track time and hours based on a 24 hour
clock format (without the bloody ":" 's). My problem is, I've a formula that
does what I need, but when I try to input that to my workbook, it doesn't
work. In my "scratch sheet" doc, it works, when I write it into my actual
book, it doesn't. Same formula. Same formatting. Doesn't work. =(S12-R12)
with cell formatting of 24 hour clock, (hr:min).

Also, =(F14-D14)/100. (fraction, 1/2's). . . How do I get this to give me
15 minute increments vice 30. I know, I know, format is 1/2's, but if I
select quarters, it doesn't calculate/display the correct time/hours.

PLEASE, can somebody help me with this? I've been banging my head against a
wall on this for a bloody month!!!!!!!!!


Thanks.

 

[Chip Pearson]

From your description, I'm assuming that your "time" values are whole
numbers representing a time. E.g, 1230 means 12:30:00 and 715 =
07:15:00. Suppose you have these times in cells A1:A6. To sum A1:A6,
use the following array formula:

=SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))

This is an array formula, so you must press CTRL SHIFT ENTER
rather than just ENTER when you first enter the formula and whenever
you edit it later. If you do this properly, Excel will display
the formula enclosed in curly braces { }. You do not type
in the braces -- Excel puts them in automatically. The
formula will not work properly if you do not enter it with
CTRL SHIFT ENTER. For much more information about array
formulas, see http://www.cpearson.com/Excel/ArrayFormulas.aspx.

The result of the formula above will be in standard Excel time, so if
you format the cell with a custom number format of [hh]:mm (the square
brackets prevent Excel from "rolling over" at 24 hours), you'll get a
time like 67:25:00. With that result in, say,B14, you can convert it
back to your pseudo time with the formula in B16 of

=(TRUNC(B14*24)*100)+MINUTE(B14)

This will change the time from 67:25:00 to 6725.

If you want to round that result in B16 to the nearest half hour, use
MROUND. (MROUND is built into Excel 2007. In 2003 and earlier, go to
the Tools menu, choose Options, and check the item for Analysis Tool
Pak). For example, in B18, use

=MROUND(B16,50)

This will return the integer 6750.

You can wrap up all of these steps into one long array formula. With
times like 1240, 715, etc in A1:A6, enter all on one line

=(ROUND((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24)*100)+
MINUTE(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))),-2))+
MROUND(((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24)*100)+
MINUTE(SUM(TIME(IF(LEN(A1:A6)=4,LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0)))-ROUND((TRUNC(SUM(TIME(IF(LEN(A1:A6)=4,
LEFT(A1:A6,2),LEFT(A1:A6,1)),RIGHT(A1:A6,2),0))*24)*100)+MINUTE(B14),-2)),30)

So, suppose A1:A6 has
1230
1545
1155
1200
800
715

the result of the formula will be 6730, indicating 67:30:00.

Yes, this is a whole lot of hoops to jump through. You're probably
better off just using real times.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]