index match returning serial date

[teamplez]

Hi all!

Been a while since my last post.

I have multiple worksheets in a workbook.

Using worksheets CIP 1.1 & 1.2 and Working Data.

Column P is completion date, formatted as a date, on CIP 1.1 & 1.2 tab.
Column F is completion date on Working Data tab. Formula is in F and dragged down accordingly.
Column B is system name on Working Data tab.
Column AC is system name on CIP 1.1 & 1.2

Formula is
=INDEX('CIP 1.1 & 1.2'!P:P,MATCH('Working Data'!B:B,'CIP 1.1 & 1.2'!AC:AC,0))&""

I am getting serial dates returned not the user friendly date. Both date referenced columns are formatted as dates.

Also, my CIP 1.1 & 1.2 worksheet is linked to a sharepoint (SP) list. I have users update the SP list. I generate metrics and reports from the linked data in the workbook.

Any and all help with ideas to resolve this formatting issue will be greatly appreciated.

 

[Aladin Akyurek]

As I dragged the formula down the range, it started populating "01/00/00" for blank entries. in my original formula I used "&""" to provide null responses as blank. any way to do that with yours?

Try...

=IF(ISNUMBER(MATCH('Working Data'!B:B,'CIP 1.1 & 1.2'!AC:AC,0)),
    TEXT(INDEX('CIP 1.1 & 1.2'!P:P,MATCH('Working Data'!B:B,'CIP 1.1 & 1.2'!AC:AC,0)),
      "m/d/yyyy"),"")