Struggling to understand function

[GreenCat223]

Hi there, I came across this function which calculates how many days occur between two dates:

=SUM(N(WEEKDAY(ROW(INDIRECT(A1&":"&A2)))=4))

I'm trying to dissect this formula to understand it better but the part I'm struggling with is the "INDIRECT(A1&":"&A2)" function. I'm not sure what job this performs in the function to help it arrive at the final result.

Please can someone explain this to me?!

Thank you!

 

[Fluff]

That formula does not calculate the number of days, but the number of Wednesdays between two dates & can be replaced by

=NETWORKDAYS.INTL(A1,A2,"1101111")

 

[GreenCat223]

Thank you for the above formula but it still doesn't answer my question. I was keen to learn and understand this "INDIRECT(A1&":"&A2)" syntax.

 

[Fluff]

In Excel dates are just numbers so 1st June 25 is 45444 & today is 45450 The indirect concatenates them together with the : & the row function will see that as row numbers & return ann array of all those rows.

 

[aarceng]

INDIRECT returns the reference specified by a text string. In this case there is a ":" so A1 and A2 would contain cell references and INDIRECT would return a range.
e.g. if A1="C4" and A2="F9" then the range returned would be C4:F9.

 

[Eric W]

Thank you for the above formula but it still doesn't answer my question. I was keen to learn and understand this "INDIRECT(A1&":"&A2)" syntax.

In newer versions of Excel (Excel 2021, Excel 365 and newer), "ROW(INDIRECT(A1&":"&A2))" would be replaced with

"SEQUENCE(A2-A1+1,,A1)".

 

[GreenCat223]

Thanks you so much all, this is really useful and good to know!

 

[Fluff]

Glad we could help & thanks for the feedback.