Complicated IF AND Statement Help

[Cantrecallmyusername]

Hi there,

I am trying to create a formula that hits a number of parameters and after several attempts I am not sure if it is actually possible - the formula needs to combine quite a number of parameters to work;
The premise for the formula is a s/s to track when people leave/join -

• hours are calculated per column based on the values in cells M2/M3 * column L (depending on region)
• when someone leave and the date matches M5 the value should be zero and continue to be zero going to the right (the date will change in each column though)
  • I thought about handling this by including an extra IF statement once the below is correctly designed where the column N looks to the left and if the value in M is zero it will return a zero
• when someone joins and the date matches M5 the value should be calculated using the hours per their region (he date will change in each column though)
  • Not sure on how to handle this one at the moment

1. IF Column F = "No" AND Column J = "Space" - M2*L6
2. IF Column F = "No" AND Column J = "Earth" - M3*L6
3. IF Column G = "Y" AND Column H = M5 AND Column J = "Space" - M2*L6
4. IF Column G = "Y" AND Column H = M5 AND Column J = "Earth" - M3*L6
5. IF Column F = M5 = 0

My formula works in places but I think as I have to many similar outcomes it is difficult to ascertain a true FALSE - as seen in the example shared it pushes the value to 0 when not meeting all the sperate condtions. Any help greatly appreciated.

=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6="",J6="SPACE"),$M$2*L6,0)))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2													36.5
3													35.0
4
5	Resource	Role	Role#2			Leaver	Joiner			Region	Gap1	No.	08/07/2022
6	Donald Duck	Duck	Duck			No				SPACE		1	36.5
7	Bugs Bunny	Bunny	Bunny			No	Y			SPACE		1	0.0
8	Pluto	Dog	Dog			08/07/2022				EARTH		1	0.0
9
10
11
Sheet1

Cell Formulas
Range	Formula
M6:M8	M6	=IF(AND(G6="Y",H6=M$5,J6="EARTH"),$M$3*L6,IF(AND(G6="Y",H6=M$5,J6="SPACE"),$M$2*L6,IF(F6=M$5,0,IF(AND(F6="No",G6="",J6="EARTH"),$M$3*L6,IF(AND(F6="No",G6="",J6="SPACE"),$M$2*L6,0)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A7:A8	Dates Occurring	yesterday	text	NO
A7:A8	Cell Value	duplicates	text	NO
A5:A6	Dates Occurring	yesterday	text	NO
A5:A6	Cell Value	duplicates	text	NO

 

[Alex Blakenburg]

You're welcome. Glad we could help.
Showing more of your data in Post #8 made it much easier to work out the requirement.
Let me know if you need help understanding it. I am sure it can be done with a more technically correct approach but since for Leaver there were possibilities of Blank, Text (No or otherwise & Date, I opted for using the Date if it existed and if not use a date after the end date of the Date range so that the If statement became a simple date comparison. Then used consistent logic for Joiner.