Most recent date = 01/00/1900 ?

[Nanogirl21]

I am using VLOOKUP to import dates from another sheet into Columns U – AV. These columns are formatted as mm/dd/yyyy. In column V I am using the =MAX function to return the most recent date in column U-AV. Since ALL cells in column U-AV are not populated with dates. They are showing as 01/00/1900. Because of this, 01/00/1900 is showing as the most recent date. Any idea how to fix this?

Side note: I already tried formatting the date columns in mm/dd/yyyy; but that didn’t work.

Thanks.

 

[Joe4]

Format will have no impact.

Note that Excel stores dates as numbers, specifically, the number of days since 1/0/1900. So a value of 0, formatted as a date, would return 01/00/1900 (or whatever date format you have chosen).
However, if you have REAL non-zero dates entered as dates in other cells, the MAX function should return one of these dates, and not the 0 value.

If it does not, it probably means one of the following things:
- You have written your MAX function incorrectly
- You do not have any dates in your range after 1/0/1900
- Your dates are actually entered as Text and not Dates

And easy way to check the last thing is to try changing the date format to some other date format.
Does that change what the values are showing up as? If not, you probably have text entries, as Formatting only affect numeric (or date) entries and does not affect text entries.

 

[mole999]

what is your vlookup up statement, also the reference list should be sorted

 

[Joe4]

the reference list should be sorted

Not necessarily. That is only required in your are using the Approximate Match argument (4th argument is TRUE).
Granted, it is usually preferable (for performance reasons), but if the results are coming back via formulas, that may not always be possible.

 

[Nanogirl21]

Not sure what you mean by reference area.

My formula is =VLOOKUP(B:B,Math!L:AV,17,FALSE) for one of the columns.

Is there any way to convert all the 01/00/1900 dates to “–“” or delete them in general before doing the -MAX function?

Breaking the =VLOOKUP formula isn’t a problem once the information is into my main worksheet called Tracking.

I believe that should solve the problem.

 

[Joe4]

Firstly, did you see my initial post with the three things to check? Please address those potential issues.
If you really are doing a MAX function and have valid dates, it shouldn't be necessary to do anything with those 0 values.
(You might want to also post your MAX function, and show us what some of the data in the range looks like).

Secondly, this formula is not technically correct:
=VLOOKUP(B:B,Math!L:AV,17,FALSE)

The first argument in the VLOOKUP function should be a single cell value, the value you want to look up on that line. Not the whole range: B:B.
While it may "work", it really isn't good form or proper.
See: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Joe4]

Is there any way to convert all the 01/00/1900 dates to “–“” or delete them in general before doing the -MAX function?

The reason you need to check the things I mentioned because if the problem is the 1st or 3rd things I listed, then getting rid of those 0 dates will not fix your problem.
I think you probably have an issue with one of those other things that needs to be addressed.

 

[Nanogirl21]

The reason you need to check the things I mentioned because if the problem is the 1st or 3rd things I listed, then getting rid of those 0 dates will not fix your problem.
I think you probably have an issue with one of those other things that needs to be addressed.

I've checked my document and everything is correct. I did make the VLOOPUP correction you mentioned.

(1) The max function is correct. It works correctly if the row that I am looking for the max date in does not have a blank date (the blank dates are appearing as 01/00/1900).
(2) I DO have dates greater than 01/00/1900.
(3) The dates that I entered is the format of mm/dd/yyyy. If there is not a date the cell is populated with a dash "-".

 

[Joe4]

(3) The dates that I entered is the format of mm/dd/yyyy. If there is not a date the cell is populated with a dash "-".

Well, that is not quite I was telling you to do there.
If you change the format to General, does it change all your dates to numbers or not?

Please post your MAX function and a simple example that is not working.

If I had three dates in A1:A3 like this:
3/3/1933
1/0/1900
5/5/1995

And I use this formula:
=MAX(A1:A3)
it returns 5/5/1995, which is correct.

I want you to show me an example where it returns 1/0/1900 when it should be returning something else.