#Value Error

[francoiscj1]

I have the following formula:

=IF(OR(D4="Expedite",D4="Urgent"),I4,WORKDAY(EDATE(MAX(H4:M4),IF(G4="Annual",12,IF(G4="Every Two Years",24,IF(G4="Every Three Years",36))))-1,1,Control!$A$1:$A$13)) in Column N

My problems: When a cell in Column G (Frequency) is "One Time", I get the !Value error. When a cell in Column G is "Annual", "Every Two Years" or "Every Three Years", I get the following (12/31/1900, 12/31/1901 or 12/31/1902). My date format is mm/dd/yyyy.

Thank you for your help.

 

[ExceLoki]

an easy fix for the #value would be

=IFERROR(IF(OR(D4="Expedite",D4="Urgent"),I4,WORKDAY(EDATE(MAX(H4:M4),IF(G4="Annual",12,IF(G4="Every Two Years",24,IF(G4="Every Three Years",36))))-1,1,$A$1:$A$13)),"once")

when i use your formula i am not getting dates in the 1900-1902 range, see below
----------------

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	12/25/2023
2	1/1/2024
3	7/4/2024						frequency	fy22	fy23	fy24	fy25	fy26	fy27	retrain date
4	11/28/2024						Every Three Years		11/29/2023					11/30/2026
5	12/25/2024						Annual		8/30/2023					8/30/2024
6	1/1/2025						Once		11/14/2023					once
7	7/4/2025			urgent			Annual		11/16/2023					11/16/2023
8	11/27/2025						Every Two Years		11/28/2023					11/28/2025
9	12/25/2025						Annual		11/14/2023					11/14/2024
10	1/1/2026
11	7/4/2026
12	11/26/2026
13	12/25/2026
Sheet1

Cell Formulas
Range	Formula
N4:N9	N4	=IFERROR(IF(OR(D4="Expedite",D4="Urgent"),I4,WORKDAY(EDATE(MAX(H4:M4),IF(G4="Annual",12,IF(G4="Every Two Years",24,IF(G4="Every Three Years",36))))-1,1,$A$1:$A$13)),"once")

Cells with Data Validation
Cell	Allow	Criteria
G4:G9	List	Once, Annual, Every Two Years, Every Three Years

 

[francoiscj1]

I can only tell you what I see on my screen even if you don't experience it on your own screen. Thank you for the help.

 

[Fluff]

In future please do not mark a post as the solution when it does not contain one. Thanks.

In your op you show one formula & complain it's returning the wrong values, but the image shows a totally different formula & totally different results.
Which are you actually using?

 

[ExceLoki]

also, please indicate which version of excel you are using. you can also add this to your profile for future posts.