Time Sheet that Auto Calculates OT and Adjusts Hours to a Max Value
Any of the grayed out fields will have automatic values. White fields will be manually filled in.
The problem is with B7 and C7. These will be gray once resolved.
B7 needs to calculate for hours in B2:B6 with a max value of 40 if C7 has a value above 0 and without that max value if C7 is 0.
C7 needs to calculate for hours in C2:C6 with a minimum value of 0. This also needs to read 0 when sick, vacation, or other is entered into D2:F6 (or D7:F7).
I've been working on this one problem for days.
In B7:
=IF(C7>0,MIN(40,SUM(B2:B6)),SUM(B2:B6))
In C7 maybe:
=IF(SUM(D7:F7)=0,MAX(0,SUM(C2:C6)),0)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
so you want any OVERTIME to be zero , if any day has an entry for sick, vacation, or other - not just that day ?C7 needs to calculate for hours in C2:C6 with a minimum value of 0. This also needs to read 0 when sick, vacation, or other is entered into D2:F6 (or D7:F7).
=IF(COUNTA($D$2:$F$6)>0,MIN(40,SUM($B$2:$B$6)),IF(C7>0,SUM(B2:B6)-SUM(C2:C6),SUM(B2:B6)))
not sure why you need this
not sure why you would not just total B - with 40 as the max value
and then
OT sum of C
probably i'm not following correctly - sorry
i have also modified the formula so you dont get value error
just need calarity on the sick/vac etc
can you give an example with those value include
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks