Summing of time Values Greater Than??

  • Thread starter Thread starter Major
  • Start date Start date
M

Major

G'day All,

I'm working on an overtime sheet and need to be able to find out the double time component (in decimal time) in a column of overtime figures. I need to be able to extract the mins or hours greater than 3:00hrs and sum them

eg:
1:30
3:40
3:50
3:55
____ _____
12.92 Total decimal Hrs 2.92 Double Time Hrs

Any help would be appreciated
 
First, I'd like to point out that total time over 3 hours
totals 2.42, not 2.92 (you included the 30 minutes from
1:30 by mistake I believe).

With that said, assuming 1:30 is in A1, enter this into B1
and fill down to B4:
=IF(24*A1>3,24*A1-3,0)

Then in B5 enter a simple =SUM(B1:B4)

-----Original Message-----
G'day All,

I'm working on an overtime sheet and need to be able to
Click to expand...
find out the double time component (in decimal time) in a
column of overtime figures. I need to be able to extract
the mins or hours greater than 3:00hrs and sum them
eg:
1:30
3:40
3:50
3:55
____ _____
12.92 Total decimal
Click to expand...
Hrs 2.92 Double Time
Hrs
 
A bit of confusion here. Too many people named Mike.

I gave you the simple =IF(24*A1>3,24*A1-3,0) and =SUM
(B1:B4) formulas. I figured it would be easier for you to
understand these ones.

The other Mike's single array formala works very much like
my set of formulas, but unless you understand the basics
first, array formulas can be tricky to understand. Array
formulas are very useful because they allow you to perform
the same operation on a large range of cells using one
formula instead of adding helper columns.

To enter an array formula you need to press
Ctrl+Shift+Enter. You can tell it's an array formula
because it's enclosed in braces {}.

To explain the other Mike's array formula:
The (A1:A4-"3:0") section simply subtracts 3 hours from
each cell using H:MM format and because this is an array,
it returns (-1:30,0:40,0:20,0:25). The (A1:A4>"3:0"*1)
portion basically says for each cell in the range, if >
then 3 hours, then return true, otherwise false. So it
returns (False, True, True, True). Then these two arrays
are multiplied. False=0 and True=1, so you get
(0,0:40,0:20,0:25). Then all these are summed using the
SUM function. This returns 1:25. Finally the /"1.0"
portion converts the H:MM format into a decimal.

Mike's method of using "3:0" and "1:0" is a bit unusal. I
have seen very few people use this. Most people would
just multiply the H:MM format by 24 to get a decimal
value, but to each his own. There's always more than one
way to get Excel to do your bidding.

Hope this helps.


-----Original Message-----
Hi Mike

This works very well!! ( =IF(24*A1>3,24*A1-3,0) )

But the array formula returns an incorrect answer.... was
Click to expand...
just trying to figure out why. I've realised that arrays
are well beyond me yet.
 
Here is a non-array formula that gives the results you describe:

=(SUMIF(A2:A5,">"&(3/24))-COUNTIF(A2:A5,">"&(3/24))*(3/24))*24

Assume times are in A2:A5

This sums all times greater than 3 hours, then subtracts (3hrs multiplied by
the number of rows with times greater than 3 hours) and puts the answer in
decimal hours. Generally, array formulas require more computational
resources than non-array formulas. Obviously, this operation alone, on such
a small range, will not have a significant observable impact.
--
Regards,
Tom Ogilvy


G'day All,

I'm working on an overtime sheet and need to be able to find out the double
time component (in decimal time) in a column of overtime figures. I need to
be able to extract the mins or hours greater than 3:00hrs and sum them

eg:
1:30
3:40
3:50
3:55
____
_____
12.92 Total decimal Hrs 2.92 Double
Time Hrs

Any help would be appreciated
 
Back
Top