How Can I Automate Leave Management to Prevent Exceeding Hour Limits?

Seeking Help with Leave Management Formula

I have a problem and I am reaching out to you to help me solve it. In my office's rules and regulations, every employee is entitled to monthly "Annual and Sick Leaves" as shown in the attachment. There is a limitation that should not be exceeded. I have highlighted the area in the attachment where I need the formula that restricts exceeding the limit. For example, when the allocated hours are 200 and it goes beyond to 232 hours.

While we can currently manage this manually, I am looking for a more efficient way to handle this automatically. How can we prevent it from exceeding 200 hours? Thank you very much in advance for your cooperation.

Best regards

Dear Parwiz, please let me know what exactly you want. I am not able to understand your requirement. Give some clarity on your expectation.

Regards,
Ganeshan Madhavi

Please find attached the file with the desired function. Please revert back if this fulfills your requirement.

Regards,
Prafulla Khedkar

You may try the attached sheet if you want the actual result, if it is less than 200.

Regards,
Praulla Khedkar

You want to check if the person exceeds 200 hours so that you can be informed and prevent them from taking additional leave. Simply navigate to the column where the hours are totaled and apply conditional formatting for values higher than 199 to highlight them. Once it reaches 200, the cell will automatically highlight. This method could assist you as the formula may show 200 even if the individual has taken more leave hours.

It appears you aim to manage employees who exceed 200 hours of leave. Your thoughts on this matter are appreciated.

Regards, Gautam Shastry

Dear Gautam Shastry,

Many thanks for the great and kind comment! Yes, I want to control the staff leave not to earn more than 200 hours, just following the organization rule!

Issue with the Formula

The formula has a problem. If an employee is entitled to 220 hours, it shows 200; it is perfect. However, if someone is entitled to less than 200 hours, it shows an error. Could you please solve this as well?!

Best Regards,

Please refer to my second file (corrected1.xls). You will get the desired result if it is less than 200, and if it is more than 200, the result will be 200.

Regards,
Prafulla Khedkar