Closed Thread
Results 1 to 11 of 11

MAX date formula displaying 00/01/1900 as blank fields in lookup

  1. #1
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    MAX date formula displaying 00/01/1900 as blank fields in lookup

    Hi, I am having difficulty with a MAX formula, where I am trying to show the latest date by looking at 3 fields. The issue is that some of the fields do not have any data in, so they are blank, which means that the MAX formula I am using finds no dates and displays the result as 00/01/1900. If this is the case I would like the field to stay blank.

    In the e.g. below you will see that in column A I want to display the max date from the columns B,C & D. This works fine where there are dates in B,C & D as in rows 2 & 3, but in row 4 there are no dates in B,C or D but the date in column A shows as 00/01/1900. How can I make it so this is blank?

    Excel-MAX.jpg

    Thanks for any help!
    Steve

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,687

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Try this: =if(and(B1="",C1="",D1=""),"",Max(B1,C1,D1))

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Alan you are a genius! Thanks so much, that worked a treat.

    Cheers,
    Steve

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Hi Steve,

    You could have kept your original formula, but just change the date format from m/d/yyyy to m/d/yyyy;;

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,687

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    @djapigo

    What happens when you change the format to add the two semi-colons. What does this do to the properties of the cell?

    Alan

  6. #6
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Hi Alan,

    From my understanding, number formatting has 4 types
    positives;negatives;zeros;text

    It's strange to think of dates as a number, but I believe this should still work.

    m/d/yyyy is considered "positive"
    the first ; says "don't show negatives"
    the 2nd ; says "don't show zeros" which is what 0/1/1900

    Does that clear it up?

    It's strange though, because the format doesn't show...

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,687

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Thanks. That makes sense to me because dates are numbers. I didn't know about the semi-colons. Need to remember that for the future.

  8. #8
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Yeah, it's a nice little hidden trick... thanks for the rep...

  9. #9
    Registered User
    Join Date
    11-01-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    45

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    Thanks guys, really appreciate your help. Both ways work, great stuff.
    Cheers

  10. #10
    Registered User
    Join Date
    10-18-2021
    Location
    Dover Ohio
    MS-Off Ver
    MS 2013
    Posts
    9

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    I have a very similar instance but want to take it a bit further. I have 2 name fields that I want to Compare. Except I want to display the name once both fields are filled in. They will be the same name so selecting whose name will not be an issue.

    =IF(OR(H56="",H61=""),"") <--- This part works great.

    If H56="" OR H61="" then display blank, but if both H56 and H61 have (A NAME IT THEM) then display (the name).

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,687

    Re: MAX date formula displaying 00/01/1900 as blank fields in lookup

    @Aronn Palmer

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #1 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1