Time formatting issues [hh]:mm

[ClaireD]

Hi there,

I a trying to add some time totals using sumif. The formula is as follows:

=SUMIFS('SheetA'!$H$14:$H$143,'Sheet B'!H$14:H$143,"TRUE")

Sheet A's reference is looking at a column that works out the time difference between two columns. The two columns are set as hh:mm. The time difference column in the formula is hh:mm.

The formula is working well and is only adding the values I want added. However I cannot get the cell formatting correct. When I try [hh]:mm I get values that are way too big (by about 100 times!). If I try hh:mm I get stupidly small values.

So one example is:
One cell is looking to add the following times hh:mm:
[TABLE="width: 81"]
<tbody>[TR]
[TD]02:28[/TD]
[/TR]
[TR]
[TD]02:26[/TD]
[/TR]
[TR]
[TD]02:00[/TD]
[/TR]
[TR]
[TD]01:10[/TD]
[/TR]
[TR]
[TD]03:28[/TD]
[/TR]
[TR]
[TD]01:39[/TD]
[/TR]
[TR]
[TD]01:40[/TD]
[/TR]
[TR]
[TD]01:11[/TD]
[/TR]
[TR]
[TD]01:25[/TD]
[/TR]
[TR]
[TD]02:26[/TD]
[/TR]
</tbody>[/TABLE]

The formula is spitting out 259:35 when formatted is [hh]:mm and 19:35 when formatted as hh:mm

I have now completely confused myself and don't even know what the right answer should be!

Thanks,

Claire

 

[jorismoerings]

Hi,

Don't know what went wrong but neither of your answers is correct. Although the second one is nearly correct but has the minutes wrong.
Correct answer is 19:53 and formatting it with [] wouldn't change the result.
If it does, some other formula's (not included in your post) are not correct.

Hope this helps.

 

[stumac]

I would guess that the underlying cell value (Which includes a date) are all a day different from each other. Format the cells with the time in them to 'dd/mm/yyyy hh:mm' they should all have the date 00/01/1900.

 

[ClaireD]

Hi,

Firstly thanks for your suggestions. I have checked the date as per stumac's suggestion but there are no dates in the formula.

I have double checked all my other formulas and none are impacting on this total so that hasn't solved the issue. Sorry jorismoerings!

Any more ideas?

 

[ClaireD]

Ok I have worked out why that value wasn't showing 19:53. The ranges of the formula were incorrect.

Anyway, it works like a dream until the value goes over 24 hours. How can I fix this?

 

[pgc01]

Ok I have worked out why that value wasn't showing 19:53. The ranges of the formula were incorrect.

Anyway, it works like a dream until the value goes over 24 hours. How can I fix this?

Hi Claire

I don't see any problem. You had the wrong ranges and got a wrong result.

Now if the ranges are correct you should get a good result with the format [h]:mm

... or, post an example like you did in post #1 and the result you are getting so that we can test.