IF AND OR
Hi I am trying to create a formula which does the following:
IFS [day] = "sat or "sun", [over time] = [hours worked], [day]<>"sat"or"sun" AND [hours worked]>8,[hours worked]8
can anyone help me with the right brackets and syntax? Basically if the site guys enter 8 hours on a sunday it should all be counted as over time, if they enter 9 hours on a Monday, one hour of that should count as over time. Hope I have explained my problem well enough
I got the if sat or sun formula to work using if(or)
=IF(OR(Day349 = "sat", Day349 = "sun"), [Total Daily Hours]349)
but when I added the next step I could not get it to work
My attempt is below:
=IF(AND(OR(Day349 = "sat", Day349 = "sun"), [Total Daily Hours]349), [Total Daily Hours]349 > 8, [Total Daily Hours]349  8)
all help much appreciated
thanks
Tom
Best Answer

I think you just need the IF statement, right? If I am reading it correctly, you want it to count all the hours as overtime if the day is "sat" or "sun", but if it's not, then subtract the total hours by 8 (if there are more than 8 hours) and return that, otherwise return 0. So the formula would look like:
=IF(OR(Day349="sat",Day349="sun"),[Total Daily Hours]349,IF( [Total Daily Hours]349 > 8,[Total Daily Hours]349  8,0))
Answers

I think you just need the IF statement, right? If I am reading it correctly, you want it to count all the hours as overtime if the day is "sat" or "sun", but if it's not, then subtract the total hours by 8 (if there are more than 8 hours) and return that, otherwise return 0. So the formula would look like:
=IF(OR(Day349="sat",Day349="sun"),[Total Daily Hours]349,IF( [Total Daily Hours]349 > 8,[Total Daily Hours]349  8,0))

hooray! thanks david, I had spent a while getting so close but not quite cracking it so thanks v much for your help, works perfectly now

Awesome! Glad it is working.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 348 Global Discussions
 199 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 282 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!