Log In
Register
Home
Active Member
October 16, 2021
Offline
HI
I am trying to get the formula below to work. I understand that the third IF is not working because the second one causes the third condition to stop.
Please help with the right structure for this formula. Spreadsheet attached.
=IF(D1="New Hire",NETWORKDAYS(D2,EOMONTH(D2,0)),IF(AND(D1="LEAVER",EOMONTH(D2,-1)+1=D2),1,NETWORKDAYS(EOMONTH(D2,-1)+1,D2,IF(AND(D1="INCREMENT",EOMONTH(D2,-1)+1=D2),0,NETWORKDAYS(EOMONTH(D2,-1)+1,D2-1)))))
Moderators
January 31, 2022
Offline
Hi, Can you please upload a file so that we don't have to guess about what you are trying to do. I pasted your formula in an empty sheet with some data in D1 and D2 and get a result. Thus, not an error. But it's difficult to debug a formula without the underlying data.
Active Member
October 16, 2021
Offline
Thanks.
File attached.
Please note that all three IFs in the formula have unique outcomes and are not interchangeable.
Active Member
October 16, 2021
Offline
HI
I figured out the correct way to structure the IF function.
=IF(D21="New Hire",NETWORKDAYS(D22,EOMONTH(D22,0)),IF(AND(D21="LEAVER",EOMONTH(D22,-1)+1=D22),1,IF(D21="LEAVER",NETWORKDAYS(EOMONTH(D22,-1)+1,D2),IF(AND(D21="INCREMENT",EOMONTH(D22,-1)+1=D22),0,IF(D21="INCREMENT",NETWORKDAYS(EOMONTH(D22,-1)+1,D22-1))))))
All sorted now.
Thank you.
Trusted Members
October 17, 2018
Offline
This is the one causing the error
(D1="LEAVER",EOMONTH(D2,-1)+1=D2)
I think a missing )
IF(D1="LEAVER",NETWORKDAYS(EOMONTH(D2,-1)+1,D2),
1 Guest(s)
