Home

Excel formula Help!!!

%3CLINGO-SUB%20id%3D%22lingo-sub-644262%22%20slang%3D%22en-US%22%3EExcel%20formula%20Help!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644262%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20to%20create%20a%20formula%20calculating%20sick%20time.%3C%2FP%3E%3CP%3EIt's%20total%20number%20of%20hours%20worked%2F30%20plus%20previous%20balance%2C%20but%20I%20only%20want%20the%20sheet%20to%20show%20a%20maximum%20of%2040%20hours.%20I%20eneter%20the%20hours%20worked%20each%20week.%3C%2FP%3E%3CP%3EA1%2045%20hour%3C%2FP%3E%3CP%3EB1%2050%20Hours%3C%2FP%3E%3CP%3EC1%2060%20hours%3C%2FP%3E%3CP%3ED1%20previous%20balance%3C%2FP%3E%3CP%3ESum(A1%3AC1)%2F30%2BD1%20Here%20is%20where%20I%20do%20not%20know%20how%20to%20add%20in%20Only%20up%20to%2040%20hours%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-644262%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-644613%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-644613%22%20slang%3D%22en-US%22%3EWrap%20your%20formula%20with%20MIN%20like%20this%3A%3CBR%20%2F%3E%3DMIN(SUM(A1%3AC1)%2F30%2BD1%2C40)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-648931%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20Help!!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-648931%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F348282%22%20target%3D%22_blank%22%3E%40aramark_176%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20also%20do%20it%20this%20way%2C%20using%20the%20IF%20function%3A%3C%2FP%3E%3CP%3E%3DIF(SUM(A1%3AC1)%2F30%2BD1%26gt%3B40%2C40%2CSUM(A1%3AC1)%2F30%2BD1)%3C%2FP%3E%3C%2FLINGO-BODY%3E
aramark_176
Occasional Visitor

I need to create a formula calculating sick time.

It's total number of hours worked/30 plus previous balance, but I only want the sheet to show a maximum of 40 hours. I eneter the hours worked each week.

A1 45 hour

B1 50 Hours

C1 60 hours

D1 previous balance

Sum(A1:C1)/30+D1 Here is where I do not know how to add in Only up to 40 hours

2 Replies
Wrap your formula with MIN like this:
=MIN(SUM(A1:C1)/30+D1,40)

@aramark_176 

You can also do it this way, using the IF function:

=IF(SUM(A1:C1)/30+D1>40,40,SUM(A1:C1)/30+D1)

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies