DATEDIF with INDIRECT function?

[DrDebit]

Is it possible to use the INDIRECT function as the third argument with the DATEDIF function?

Today	Birthday	Y	M	D	MD	YM	YD
11/5/2021​	10/5/1979​	42​	#REF!​	#REF!​	#REF!​	#REF!​	#REF!​

The function that I am using in C2 is =DATEDIF($B$2,$A$2,"Y") ... perfect...
...but in D2 is =DATEDIF($B$2,$A$2,INDIRECT(D1))...I get a #REF! error
Why doesn't it work? Is there a way to make it work?
Thank you.

 

[Peter_SSs]

You don't need INDIRECT to do what you want.

21 11 06.xlsm
	A	B	C	D	E	F	G	H
1	Today	Birthday	Y	M	D	MD	YM	YD
2	5/11/2021	5/10/1979	42	505	15372	0	1	31
DATEDIF

Cell Formulas
Range	Formula
C2:H2	C2	=DATEDIF($B$2,$A$2,C$1)

 

[FMHasan]

Work off day count.xlsx
	A	B	C	D	E	F	G	H
1	Supporting	Name List	Y	M	D	MD	YM	YD
2		Indirect Value	Y	M	D	MD	YM	YD
3
4	Today	Birthday	Y	M	D	MD	YM	YD
5	06-Nov-2021	10-May-1979	42	509	15521	27	5	180
Sheet2

Cell Formulas
Range	Formula
C5	C5	=DATEDIF($B$5,$A$5,INDIRECT(Y))
D5	D5	=DATEDIF($B$5,$A$5,INDIRECT(M))
E5	E5	=DATEDIF($B$5,$A$5,INDIRECT(D))
F5	F5	=DATEDIF($B$5,$A$5,INDIRECT(MD))
G5	G5	=DATEDIF($B$5,$A$5,INDIRECT(YM))
H5	H5	=DATEDIF($B$5,$A$5,INDIRECT(YD))

Named Ranges
Name	Refers To	Cells
D	=Sheet2!$E$2	E5
M	=Sheet2!$D$2	D5
MD	=Sheet2!$F$2	F5
Y	=Sheet2!$C$2	C5
YD	=Sheet2!$H$2	H5
YM	=Sheet2!$G$2	G5

 

[Peter_SSs]

@FMHasan @DrDebit
Whilst that works, creating the named ranges is not needed as demonstrated above.
Even if you do create the named ranges as you have, there is still no need o to use INDIRECT as shown below. See also the final bullet point below.

21 11 06.xlsm
	A	B	C	D	E	F	G	H
1	Supporting	Name List	Y	M	D	MD	YM	YD
2		Indirect Value	Y	M	D	MD	YM	YD
3
4	Today	Birthday	Y	M	D	MD	YM	YD
5	06-Nov-21	10-May-79	42	509	15521	27	5	180
DATEDIF (2)

Cell Formulas
Range	Formula
C5	C5	=DATEDIF($B$5,$A$5,Y)
D5	D5	=DATEDIF($B$5,$A$5,M)
E5	E5	=DATEDIF($B$5,$A$5,D)
F5	F5	=DATEDIF($B$5,$A$5,MD)
G5	G5	=DATEDIF($B$5,$A$5,YM)
H5	H5	=DATEDIF($B$5,$A$5,YD)

Named Ranges
Name	Refers To	Cells
D	='DATEDIF (2)'!$E$2	E5
M	='DATEDIF (2)'!$D$2	D5
MD	='DATEDIF (2)'!$F$2	F5
Y	='DATEDIF (2)'!$C$2	C5
YD	='DATEDIF (2)'!$H$2	H5
YM	='DATEDIF (2)'!$G$2	G5

Further,

• using the named ranges requires every formula to be entered separately whereas the formula I suggested in post #2 can be just copied across.
• INDIRECT is a volatile function so really should be avoided wherever there is a viable alternative, and there certainly is here.

 

[DrDebit]

Thank you so much. Silly me.

David

You don't need INDIRECT to do what you want.

21 11 06.xlsm
	A	B	C	D	E	F	G	H
1	Today	Birthday	Y	M	D	MD	YM	YD
2	5/11/2021	5/10/1979	42	505	15372	0	1	31
DATEDIF

Cell Formulas
Range	Formula
C2:H2	C2	=DATEDIF($B$2,$A$2,C$1)

[/RANGE

 

[Peter_SSs]

You're welcome.