Stop formula when a value is reached


I’m trying to create a formula for a timesheet. The idea is that whenever a line item reaches 40 hours, it will stop adding more hours. the issue is that since the employees are working different hours a days, I can’t figure out how it properly stops on 40 hours for each employer. Just to give more context, I’m trying to allocate the hours worked into each stage of the project, and I also want to understand the overtime and regular hours, so I do have my total hours breakdown into regular and overtime but I want this to be breakdown further into each stage (example: Project Stage 1 Regular and Project Stage 1 Overtime).

I manage to get closer by using this formula:

if({Work Sunday} = “Project Set Up”, if({Sunday} <= 40, {Sunday}, “0”), “0”) + if({Work Monday} = “Project Set Up”, if( {Monday ACUM} <= 40, {Monday}, “0”), “0”) + if({Work Tuesday} = “Project Set Up”, if({Tuesday ACUM} <= 40, {Tuesday}, 40 - {Monday ACUM}), “0”) + if({Work Wednesday} = “Project Set Up”, if({Wednesday ACUM} <= 40, {Wednesday}, 40 - {Tuesday ACUM}), “0”) + if({Work Thursday} = “Project Set Up”, if({Thursday ACUM} <= 40, {Thursday}, 40 - {Wednesday ACUM}), “0”) + if({Work Friday} = “Project Set Up”, if({Friday ACUM} <= 40, {Friday}, 40 - {Thursday ACUM}), “0”) + if({Work Saturday} = “Project Set Up”, if({Saturday ACUM} <= 40, {Saturday}, if(({Thursday ACUM} + 40 - {Thursday ACUM}) = 40, “0”, 40 - {Friday ACUM})), “0”)

This is how it looks like, the Dup of Total is the column where I want the formula to work on:


1 Like

While this might not be a formula column-specific idea, have you checked out the Workload Widget/View? This tool allows you to manage employees’ schedule, hours, projects, and more. You can set a cap for the number of hours they can work.

Check it out here:

Let me know if this seems helpful for you! :blush: