Is there a way to extract a formula result from a formula to do further math on it?

Question

This function formula looks in the specified filepath in cell A2 and returns the filename based on the file extension in cell B2:

Formula in Cell A4 : =IFERROR(INDEX(GetFileNamesbyExt($A$2,$B$2),ROW()-2),"")

Result Cell A4 : Servicing Balance Allocation Report 1-13-2020.xlsx

The problem though is, I need to be able to extract the number 13 in the above Result so I can do math with it. Problem I run into is that since the result is a formula result, I can't seem to extract the day portion.

I don't mind if someone has a VBA solution, whatever works.

The goal is to be able to get the number of days difference between the two most recent files based on the date in the file string. For example:

Difference between:

Servicing Balance Allocation Report 1-13-2020.xlsx

Servicing Balance Allocation Report 1-17-2020.xlsx

= 4

Answer 1

Consider:

=LEFT(MID(A4,FIND("-",A4)+1,999),FIND("-",MID(A4,FIND("-",A4)+1,999))-1)

basically getting the text between the dashes.

NOTE:

It is easier to see in two cells. Say in D1 put:

=MID(A4,FIND("-",A4)+1,999)

and in E1 put:

=LEFT(D1,FIND("-",D1)-1)

Answer 2

To get the day use:

=DAY(--TRIM(LEFT(MID(A4,SEARCH("??-*-????.",A4),999),FIND(".",MID(A4,SEARCH("??-*-????.",A4),999))-1)))

to get the days between we can drop the DAY() and just subtract the dates:

=TRIM(LEFT(MID(A5,SEARCH("??-*-????.",A5),999),FIND(".",MID(A5,SEARCH("??-*-????.",A5),999))-1))-TRIM(LEFT(MID(A4,SEARCH("??-*-????.",A4),999),FIND(".",MID(A4,SEARCH("??-*-????.",A4),999))-1))