Date Formula Error

[francoiscj1]

The following formula is in my Column N:

=IFERROR(IF(OR(ISNUMBER(SEARCH("Expedite",D4)),ISNUMBER(SEARCH("Urgent",D4))),I4,WORKDAY(MAX(IF(ISNUMBER(I4),I4,H4),J4,K4,L4,M4),IF(G4="Annual",WORKDAY(EDATE(I4,12),1,Control!$A$1:$A$13),IF(G4="Every 2 Years",WORKDAY(EDATE(I4,24),1,Control!$A$1:$A$13),IF(G4="Every 3 Years",WORKDAY(EDATE(I4,36),1,Control!$A$1:$A$13),""))))),"")

For example, in N4, the date should be calculating three years from the date in I4 (11/29/2026). There is no date for H4 since the potential employee began work in 2023. What am I doing wrong?

Attached is a screenshot.

 

[6StringJazzer]

The first time you are using WORKDAY, the first argument is a date, then the second argument is supposed to be a number of days. But in your formula, it's the date that you calculate in the last WORKDAY. So I think you have your parentheses jumbled up.

I'm not sure what the correction is because I don't know what you are trying to do.

What is the number of days you want to use if D4 is Expedite or Urgent?

Here is how your formula is being evaluated for that row (an excellent debug tool that you should learn)

The date is calculated correctly here as 11/30/2026, in your last WORKDAY

But on completion that result is used as the # of days in the first call to WORKDAY, giving you a date 46,356 days after 11/29/2023.

 

[francoiscj1]

If the "Expedite" or "Urgent" is in D4, the course should be done on the same day.

What I'm trying to do is each time a date is inserted in H4:M4, the formula in N4 is reflected to calculate the frequency in G4

 

[felixstraube]

I am guessing that on the Control page in cells A1:A13 you have your holidays. Then... how about this?:

DateFormulaError.xlsx
	D	E	F	G	H	I	J	K	L	M	N
3	Value			Frequency	FY22	FY23	FY24	FY25	FY26	FY27	Retraining date
4				Every 3 Years		29/11/2023					30/11/2026
5				Annual		30/08/2023					30/08/2024
6				Once		14/11/2023
7				Annual		16/11/2023					18/11/2024
8				Every 2 Years		16/11/2023					17/11/2025
9				Annual		28/11/2023					28/11/2024
10				Annual		14/11/2023					14/11/2024
Hoja1

Cell Formulas
Range	Formula
N4:N10	N4	=IFERROR(IF(OR(D4="Expedite",D4="Urgent"),I4,WORKDAY(EDATE(MAX(H4:M4),IF(G4="Annual",12,IF(G4="Every 2 Years",24,IF(G4="Every 3 Years",36))))-1,1,Control!$A$1:$A$13)),"")

 

[francoiscj1]

I am guessing that on the Control page in cells A1:A13 you have your holidays. Then... how about this?:

DateFormulaError.xlsx
	D	E	F	G	H	I	J	K	L	M	N
3	Value			Frequency	FY22	FY23	FY24	FY25	FY26	FY27	Retraining date
4				Every 3 Years		29/11/2023					30/11/2026
5				Annual		30/08/2023					30/08/2024
6				Once		14/11/2023
7				Annual		16/11/2023					18/11/2024
8				Every 2 Years		16/11/2023					17/11/2025
9				Annual		28/11/2023					28/11/2024
10				Annual		14/11/2023					14/11/2024
Hoja1

Cell Formulas
Range	Formula
N4:N10	N4	=IFERROR(IF(OR(D4="Expedite",D4="Urgent"),I4,WORKDAY(EDATE(MAX(H4:M4),IF(G4="Annual",12,IF(G4="Every 2 Years",24,IF(G4="Every 3 Years",36))))-1,1,Control!$A$1:$A$13)),"")

Thank you so much!! It worked!!!

 

[felixstraube]

Happy to help. Thanks for the feedback.