Formulas and functions

Copper Contributor

I am using a PC and have windows 10.  I need help I am trying to do time sheets for my place of work.  I'll explain what I'm trying to do as it can be confusing.  So we have to work 168 hours in a 28 day schedule, however our time sheets need to be 31 days.  We get overtime pay at 171 hours and above in 28 days.  I have a time sheet that has 2 28 days ending in it and can't get it to pull the hours 171 and above out of the regular hours.  When I enter the formula twice it gives me #VALUE!  Any help would be greatly appreciated.   Here is the formula: =IF(((SUM('Feb-Mar-22'!D8:D34,'Mar-Apr-22'!D4:D4))>171),(171-(SUM('Feb-Mar-22'!D8:D34,'Mar-Apr-22'!D4:D4))+(IF(((SUM(D5:D32))>171),(171),(SUM(D5:D32))))),(SUM('Mar-Apr-22'!D4:D4,'Mar-Apr-22'!D5:D32))),(IF(((SUM('Mar-Apr-22'!D4:D4,'Mar-Apr-22'!D5:D32))>171),(171-(SUM('Mar-Apr-22'!D4:D4,'Mar-Apr-22'!D5:D32))+(IF(((SUM(D33:D34))>171),(171),(SUM(D33:D34))))),(SUM('Mar-Apr-22'!D5:D32,'Mar-Apr-22'!D33:D34))))

 

Thank you!

4 Replies

@Jhubbard78 

 

As a general rule, the experts (i.e., the people that write the textbooks on how to use Excel) warn against deeply nested IF formulas. They have a tendency to become unintelligible. A far better alternative would be to break each of your various IF conditional statements as a stand-alone conditional, and then have a single summarizing IF that brings the results of those individual statements together. 

 

In fact, I see you're referring to other sheets within these formulas as well. It might even make sense to take such sub-formulas as SUM('Feb-Mar-22'!D8:D34,'Mar-Apr-22'!D4:D4) and give it its own cell, and then name that cell, so that in subsequent formulas you're referring to FebAprTot or some such mnemonic value. (Do you know about "named ranges" and their utility in writing Excel formulas? If not, here's a useful reference: https://exceljet.net/glossary/named-range

 

Breaking things apart like that -- we refer to doing this as using "helper columns" -- can highlight where one or another of the many components might be delivering up that #VALUE error message.

 

That said, if you could post a copy of the spreadsheet/workbook (after converting any real names to fictitious ones), it would be easier to help.

@mathetesOk I'm going to probably sound dumb, but how do I attach a copy of the workbook to this?  I don't know much about excel except what I"ve watched on youtube and self taught. 

@Jhubbard78 

 

To attach a file, first click on "Reply" 

then on "Open full text editor"

mathetes_0-1641492205613.png

Then down at the bottom of that window, you should see

mathetes_1-1641492259902.png

where you can drag and drop the file.

 

IF that doesn't work, which is possible, then we will need to exchange the file by Personal Messaging, which is possible through clicking on the user name (in my case, it's the @mathetes ) which will open a user profile. Up in the top right hand corner there, you'll see a box to click and send a message. Files can be attached there which only I will see. (I was an HR professional before I retired, and am used to handling highly confidential files, if that's reassuring.)

for some reason it won't give me that drop down file. so I personal messaged you. Thank you so much for your help!