Applying a limit to a cell adding up hours worked.

[OGCarroll]

Hello All,

I'm having an issue with a timesheet I'm building for my companies Engineers. The way it works here is that after 50 hours worked in the week, they get paid over time.

What I want is for I38 to add up all the hours from the I column, but to not exceed a maximum of 50:00:00.

I've been racking my brain over this, trouble is I've just come back to using Excel after not using it for the better part of a decade so I'm rusty as hell.

Can anybody help solve this issue for me? For what its worth, the cell I38 is formatted as Custom in hh:mm:ss

Thank you in advance everyone.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=MIN(2+TIME(2,0,0),SUM(I3:I37))

 

[OGCarroll]

Hi & welcome to MrExcel.
How about

=MIN(2+TIME(2,0,0),SUM(I3:I37))

Hello Fluff,

Thank you very much for taking time to reply to my thread, and for the warm welcome!

I tried the formula you provided, but unfortunately this makes I38 return "02:00:00"

 

[Fluff]

Make sure the cell is formatted as [h]:mm:ss

 

[OGCarroll]

Make sure the cell is formatted as [h]:mm:ss

My goodness that worked!

Thank you so much Fluff you've saved me such a headache I would have never attempted the formula you provided above.

This forum (and yourself) are a life saver!

 

[Fluff]

You're welcome & thanks for the feedback.