status bar sum is correct while column sum is not

[lakke2120]

Hello, I have been reading for hours and i have not been able to find someone who can answer why the time on the excel status bar is correct and my column sum is not. The column sum is adding minutes which I cannot figure out. I also need to sum the hours to the nearest 15 minute increment. I've tried sum and round formulas that I read about but kept getting value errors. If you can help thank you very much.

 

[Fazza]

both sums are correct, you need to consider the cell formats

the status bar shows the sum in the same format as the selected range

the worksheet cell has a different format to show the value 56.4166666666666666666667

 

[Jerry Sullivan]

I see Fazza got a post in while I was mocking up this screenshot.

Below you can see what Fazza described. Both columns are the exact same numbers formatted differently (column C just references the adjacent cell in B).

Excel 2016 (Windows) 32 bit
	A	B	C
1		"h:mm:ss"	"0.000000"
2		Time Format	Number Format
3
4		0:05:00	0.003472
5		0:15:00	0.010417
6		0:45:00	0.031250
7		1:30:00	0.062500
8	Sum	2:35:00	0.107639
9
10	Round	2:30:00	0.104167
Sheet1

Cell Formulas
Range	Formula
C4	=B4
B8	=SUM(B4:B7)
B10	=ROUND(B8*96,0)/96

Regarding rounding to the nearest 15 minutes, that's a little trickier than decimal rounding because the decimal increment is 1/96 (because 1 day = 1 and 15 minutes = 1/96th of a day).

The formula in B10 shows one way to round to the nearest 1/96th.