Here’s my best shot at describing my problem. I am setting up a time card for tracking employee work hours. I want to include a rolling total of accumulated sick time, which is accrued at 5 hours 32 minutes (5:32) per pay period. Everyone has a pre-existing balance they’ve accumulated since they’re initial employment. Their total balance cannot exceed 1024 hours (1024:00). In order to track ongoing usage there is also a cell for entering usage for the current period and one for balance for the period. My problem comes from h:mm format and an IF statement regarding the maximum allowable hours (1024:00).

Cell M13 is defined as the additional time (5:32) accrued per pay period.
Cell M14 is the pre-existing balance of accrued time prior to this spreadsheet
Cell M15 is the amount of sick time used within the current pay period. There are two defined uses for sick time. One being excused sick time and the other being personal days. The IF formula given below SUMS all the “P” personal and sick time for the pay period.
Cell M16 calculates the ending pay period balance, as given below. As far as calculating the balance everything works. When I try to limit it to the 1024:00 maximum allowable is where the problem starts.

M13 = 5:32 (formatted as [h]:mm)
M14 = Pre-Existing Balance (formatted as [h]:mm)
M15 = F29+SUMIF(J13:J19,"P",I13:I19)+SUMIF(J21:J27,"P",I21:I27) (formatted as [h]:mm and all associated time values formatted the same)
M16 = calculated as M14-M15+M13 works as expected (correctly) when attempting to limit or cap the value to 1024:00 using IF(P23<TIMEVALUE("1024:00"),"M14-M15+M13","1024:00") it is an “EPIC FAILURE”!!!

Any suggestions??? Thanks in advance for your time and assistance!!!