Nested IF formula with multiple criteria not working

[CaptainGravyBum]

OK, I've tried my best, but I just can't find out what is going on with this formula. Chat GPT is usually a little helpful, but even that is stumped - you can't beat good old fashioned brain power so I'm hoping you can help.

The link to my file is below, I'm trying to add a formula in column R of the upload template sheet which looks for various criteria to be met before providing a result - mainly because there is a lot of error checking due to the data being provided by various people who can make errors. I can get cell R2 to return 8:00 which is correct, but then everything else is false, as though it looks at the first IF and ignores all others. At the moment it's ignoring the cells which don't return a time which is what I want, but also returning false for the cells where there should be data.

=IFERROR(
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("AM",Q2)), 'Vigo Data'!AF1 >= TIME(5, 0, 0), 'Vigo Data'!AF1 <= TIME(11, 59, 0)), 'Vigo Data'!AF1,"")&
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("PM", Q2)), 'Vigo Data'!AF1 >= TIME(12, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")&
IF('Vigo Data'!AF1=0,IF(AND((SEARCH("TIME", Q2)), 'Vigo Data'!AF1 >= TIME(0, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")))),"")

Upload Master Copy.xlsm

Shared with Dropbox

www.dropbox.com

 

[dreid1011]

Your conditions seem to be conflicting with each other. First, you are testing if 'Vigo Data'!AF1 is equal to 0. If it is, then you are testing if it is between two specific values.. which are both larger than 0.

So when the first IF is true, the rest will be false. Conversely, if the first IF is false, the rest could be true, but it won't even get there.

 

[dreid1011]

I don't know if this solves the problem, but it makes sense to me (unable to download your sheet at work):

=IFERROR(IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("AM",Q2)), 'Vigo Data'!AF1 >= TIME(5, 0, 0), 'Vigo Data'!AF1 <= TIME(11, 59, 0)), 'Vigo Data'!AF1,"")&IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("PM", Q2)), 'Vigo Data'!AF1 >= TIME(12, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")&IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("TIME", Q2)), 'Vigo Data'!AF1 >= TIME(0, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,""),""),""),""),"")

 

[CaptainGravyBum]

Hi @dreid1011,
Thanks for your reply. The first test is supposed to say, if the cell is greater than 0 although it doesn't seem to be working anyway. I think I need to reshuffle the formula a bit, because yours is returning results where there is a time already on the sheet, but If there isn't anything it should result in the manually entered time I have specified.
Weird thing is that the time on my upload sheet (from the formula result) is only showing a decimal and will not allow me to change it to any other format - bit confused why that would be happening.

 

[dreid1011]

Hi @dreid1011,
Thanks for your reply. The first test is supposed to say, if the cell is greater than 0 although it doesn't seem to be working anyway. I think I need to reshuffle the formula a bit, because yours is returning results where there is a time already on the sheet, but If there isn't anything it should result in the manually entered time I have specified.
Weird thing is that the time on my upload sheet (from the formula result) is only showing a decimal and will not allow me to change it to any other format - bit confused why that would be happening.

If you can provide a small sample of data and the expected results, I'm sure we can come up with a solution.

 

[CaptainGravyBum]

Not really sure how this XL2BB malarky works, but here is the main sheet it created:

Upload Copy.xlsm
	U
10
Upload Template

Which references this sheet:

Upload Copy.xlsm
	G
12
Vigo Data

Hope this helps, I did get it to return values for the first two rows, but it also returned the same value for the cells where there shouldn't be data - so that didn't work.

 

[dreid1011]

You have to select the whole range of data before you create the mini sheet.

 

[CaptainGravyBum]

Whoops, try again.

Upload Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Docket	Order_No	Despatch Date	Requesting Depot	Collect Depot	Consignor Name	ConsignorPostCode	Consignee Name	Consignee Address 1	Consignee Address 2	Consignee Address 3	Consignee Address 4	Consignee Postcode	Delivery Depot	Trunk	Service	Delivery Time
2																	AM	0.333333333333333
3																	PM	0.583333333333333
4																	TIME	11:00
5																	ND	11:00
6																	EC	11:00
7																	ND	11:00
8																	ND	11:00
Upload Template

Cell Formulas
Range	Formula
Q2:Q8	Q2	=VLOOKUP('Vigo Data'!AD1,Instructions!Z:AB,3,0)
R2:R8	R2	=IFERROR(IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("AM",Q2)), 'Vigo Data'!AF1 >= TIME(5, 0, 0), 'Vigo Data'!AF1 <= TIME(11, 59, 0)), 'Vigo Data'!AF1,"")&IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("PM", Q2)), 'Vigo Data'!AF1 >= TIME(12, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,"")&IF('Vigo Data'!AF1>0,IF(AND(ISNUMBER(SEARCH("TIME", Q2)), 'Vigo Data'!AF1 >= TIME(0, 1, 0), 'Vigo Data'!AF1 <= TIME(23, 59, 0)), 'Vigo Data'!AF1,""),"10:00"),"14:00"),"11:00"),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R1:R3,R5:R1048576,R4:S4	Cell Value	="ENTER TIME"	text	NO

Upload Copy.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1																													GB	24A	26/02/2024	08:00
2																													GB	24P	26/02/2024	14:00
3																													GB	24T	26/02/2024
4																													GB	24	26/02/2024
5																													GB	48	27/02/2024
6																													GB	24	26/02/2024
7																													GB	24	26/02/2024
Vigo Data

 

[dreid1011]

Another note, you don't have to select from A1 to your data, you could also just select AC1:AF7 since that seems to be the relevant section. But now I can see what to work with better. Thank you.

 

[CaptainGravyBum]

Another note, you don't have to select from A1 to your data, you could also just select AC1:AF7 since that seems to be the relevant section. But now I can see what to work with better. Thank you.

Yeah, that was just to make sure the columns where the correct letters.