date coming up as 00/01/1900 after using max function

[smj123]

Hi there,

I need some help please! I'm linking with another document to find dates for when people need to attend refresher courses.

The other document was created by someone else, and I don't have permission to modify the other document to change the format of the dates, so the dates that were linking were coming up as 00/01/1900.

I used this to make them come up as blanks instead of 00/01/1900 in my workbook and it worked just fine:

=IF('[X3_X3.5 2011 ymlaen.xlsx]PRIF RESTR'!V6="","",'[X3_X3.5 2011 ymlaen.xlsx]PRIF RESTR'!V6)

However, now I have another column in my current workbook which selects the latest date in the row, so that I could see if they need to be re-trained or not, so I used a basic MAX function (=MAX(D2:I2)). It works fine for entries that have dates in the columns. However, some entries have no dates in their columns yet because they haven't completed their course yet, however, once these dates are added onto the original form they will link up with my current one and the max function will make sense.

What I need is for the MAX function to produce a blank if the other entries are blank instead of 00/01/1900.

I hope this makes sense! I know it's probably very basic but I can't work it out.

Regards,
SMJ

 

[Robert Mika]

Try this:
=IF(MAX(D2:I2)=0,"",MAX(D2:I2))

 

[jasonb75]

Custom fomatting also works.

[=0]"";dd/mm/yyyy;@

 

[smj123]

Great, thank you! Sorted now!

smj