I have a date tab with dates (Column A "dd/mm/yyyy", Column B "MMM-yy" and Column C "yyyy-yy" [financial year]). I work in a hospital. I have a list of surgeries with the date ("dd/mm/yyyy") they were performed.

I have a Vlookup to obtain the "MMM-yy" data (for my monthly pivot tables) as follows:

=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))

I came across an error in my source tab - all dates in March 2017 had "MAR-16" in Column B. I changed these, manually, to "'MAR-17".

The Vlookup continues to work every single month but for MAR-17.

When formatted as General (as all the others are), it returns "42795". When I change it to Custom format "mmm-yy" (which none of the others, which work fine, are), it returns "Mar-17" but in the pivot table comes up as "1-Mar-2017" whereas all the others are "FEB-17" etc).

Any help would be great.

Thanks in advance!

I'm not near Excel right now to try anything, but my guess is that all the working"dates" are not actualdatesas far as Excel is concerned. My guess is that they are actually text, which may explain why they don't show up in your pivot table with the "day" portion or display the serial number like the Mar-17 cells do.Excel can be weird when it comes to dates/text, especially if the "dates" were imported from another source. They often come over as text and is can be troublesome to convert them to dates that Excel will recognize.

In your case, you may have the opposite problem. I think you have to change your Mar-17 to Text in order for your VLOOKUP to work like the others. That can also be troublesome because Excel will often be reluctant to give up a Date format once it is set. You could try adding a single quote before a few of your Mar-17 entries to force them to Text and see how Excel handles them.

Like I said...just a guess.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

BTW...this is not directly related to your question...it's just a suggestion about your VLOOKUP formula. The IFERROR function was introduced in Excel 2007. This makes writing VLOOKUP formulas a little easier:

=IFERROR(VLOOKUP($B39,Dates,2,0),"")

This formula will return the empty string if the lookup_value isn't found in the lookup_table.

Now for the downside of using IFERROR:

The standard method, using = IF(ISNA...) directly checks for the ISNA error returned by VLOOKUP. The IFERROR function looks for

anyerror related to the VLOOKUP.The implication of this in your case is as follows:

=IF(ISNA(VLOOKUP($B39,Dates,2,0)),"",VLOOKUP($B39,Dates,2,0))

There are 2 possible errors that that formula can produce: #N/A and #NAME. Your formula will handle the #N/A error and return the empty string, but if the Named Range "Dates" doesn't exist, it will return the #NAME error. That's a good thing since you'll know that you are referencing an invalid range.

The IFERROR function will return the empty string for

bothof those errors but you will not know why. Let's take the situation where the userthinkshe created the named range but forgot. When the IFERROR returns the empty string, he might think it's because the lookup_value doesn't exist. That'snota good thing.So, considered yourself warned ;-). IFERROR can save some typing, but it does have some inherent dangers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History