Hours Worked Running Total Stops Calculating Correctly

[wsnyder]

Hi all,

Excel 365 Version 2109 Build 14405.20002 Beta Channel.

Hours Worked Running Total Stops Calculating Correctly on Row 6 in the attached
I copied the formula on row 5 down - still incorrect.
Any ideas?

Hrs Worked.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Effective Date	Date	Day	Time In	Lunch Out	Lunch In	Time Out	Hrs Worked Minutes	Running	Total Hours	Hours Nearest Quarter
2	8/16/2021	8/14/2021	Sat	16:53			17:20	0:27	0:27	0.00
3	8/16/2021	8/16/2021	Mon	8:16	12:08	13:11	16:20	7:01	7:28	0.00
4	8/17/2021	8/17/2021	Tue	7:57	12:00	12:30	16:35	8:08	15:36	0.00
5	8/18/2021	8/18/2021	Wed	8:03	12:07	13:10	16:30	7:24	23:00	0.00
6	8/19/2021	8/19/2021	Thu	8:00	12:00	12:30	16:30	8:00	7:00	0.00
7	8/20/2021	8/20/2021	Fri	8:00	12:00	12:30	16:30	8:00	15:00	0.00
Sheet1

Cell Formulas
Range	Formula
H2:H7	H2	=G2-D2-(F2-E2)
I2,C2:C7	I2	=H2
J2:J7	J2	=Q2
I3:I7	I3	=I2+H3
A3:A7	A3	=B3
B4:B7	B4	=+B3+1

Thanks
-w

 

[Toadstool]

Hi Wsnyder,

Try formatting as [h]:mm

 

[wsnyder]

Hi Wsnyder,

Try formatting as [h]:mm

Thanks, no help.
Not surprising since I copied cell above down: formula, format, everything.

Thanks anyway,
-w

 

[Toadstool]

Did you remember the square brackets?

 

[wsnyder]

Did you remember the square brackets?

View attachment 45181

Thanks,

That is odd. row 5 was showing the correct running total w/o the correct number format
So I copied down assuming it would work on row 6 - which it did not.

I updated all rows to number format [h]:mm and now showing the correct running total.

thanks,
-w

 

[Toadstool]

The format h:mm is for time of day which is limited to 24 hours. Once you go over 24 hours you need the [h]:mm format to show elapsed time over 24 hours. It's the format, not the formula.

 

[wsnyder]

Aha! Thanks for the explanation
-w