Formula problem

[howard]

I have formula below which extract the highest month in a column from another workbook.

where the result is less than the current month, I want to use the current month and year, but cannot get the formula to work. The current month and year is in cell E1

 =if(TEXT(MAX('C:\My Documents\[BR 898885 (91587 -sales).xls]Details'!$C$1:$C$65536),"mmmm-yyyy)<E1,E1),TEXT(MAX('C:\My Documents\[BR 898885 (91587 -sales).xls]Details'!$C$1:$C$65536)

It would be appreciated if someone can assist me

 

[etaf]

i think you have a bracket in the wrong place and a missing "
=if(
TEXT(MAX('C:\My Documents\[BR 898885 (91587 -sales).xls]Details'!$C$1:$C$65536),"mmmm-yyyy")<E1,
E1),
TEXT(MAX('C:\My Documents\[BR 898885 (91587 -sales).xls]Details'!$C$1:$C$65536
)

is E1 formatted as a date or also TEXT in the same format as MMMM-YYYY ?
if it does not work after removing the bracket
try
Just using in a cell

TEXT(MAX('C:\My Documents\[BR 898885 (91587 -sales).xls]Details'!$C$1:$C$65536),"mmmm-yyyy)
what do you return
is it a number or the date format , change format to general and it shoulkd stay the same

change format of E1 to general and for it to work you should still see MMMM-YYYY format

A date is a number and text will not be seen as a date and so not be less than

 

[howard]

Thanks for pointing out my error. Problem now resolved

 

[etaf]

you are welcome

 

[jasonb75]

Thanks for pointing out my error. Problem now resolved

Just looking at your question and Wayne's reply, have you amended your dates to proper dates now? If not then you will still likely encounter problems.

With text dates in the format of "mmmm-yyyy" the months will evaluate alphabetically, i.e. your formula of TEXT(....)<E1 would see the months in the order of April, August, December, February, January, July, June, March, May, November, October, September.
Also, if the date range covers more than 1 year then it would mean that (for example) August 2020 would be before December 2019 in the alphanumeric hierarchy.

 

[howard]

Hi Jason

Thanks, the dates are correctly formatted