Problems with Time Format

  • Thread starter Thread starter Troy
  • Start date Start date
T

Troy

Sheet 1:
Cells are formatted at [h]:mm so it will be continuous
hours like 25:00 instead of 1:00. This is exactly what I
want.

Sheet 2:
(Here's the problem)
I have this formula:

=MOD(SUMIF(Sheet 1!$AI$7:$AI$4008,10,Sheet 1!B$7:B$4008),1)

AI column recalls the numeric month in A7:A4000

I did besure to format the cell with [h]:mm and it won't
go any higher than 23:59
I need it to go very high like 1000:00, can yo help me out?
 
By wrapping your SUMIF with MOD(...,1) you eliminate the integer
part of the sum - i.e, all you're left with is the time < 24 hours.

i.e,

1000:00 is the formatted display of 41.66666667

MOD(41.66666667, 1) = 0.66666667

which will display as

16:00

Use

=SUMIF(Sheet1!$AI$7:$AI$4008,10,Sheet1!B$7:B$4008)

instead.
 
Back
Top