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.

 

[Norie]

Have you tried formatting the cell with the formula with an appropriate date format?

 

[teamplez]

Column F is formatted as a date.

 

[teamplez]

Both date columns are formatted with the same formatting.

 

[Aladin Akyurek]

Care to post the result that you get?

 

[teamplez]

Some of the returned data:
[TABLE="width: 91"]
<colgroup><col></colgroup><tbody>[TR]
[TD]42345[/TD]
[/TR]
[TR]
[TD]42612
[/TD]
[/TR]
[TR]
[TD]42612[/TD]
[/TR]
[TR]
[TD]42347[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
[TR]
[TD]42612
[/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Some of the returned data:
[TABLE="width: 91"]
<tbody>[TR]
[TD]42345[/TD]
[/TR]
[TR]
[TD]42612[/TD]
[/TR]
[TR]
[TD]42612[/TD]
[/TR]
[TR]
[TD]42347[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
[TR]
[TD]42612[/TD]
[/TR]
[TR]
[TD]42536[/TD]
[/TR]
</tbody>[/TABLE]

Try...

=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")

Adjust the format instruction "m/d/yyyy" to suit.

 

[teamplez]

Morning/Afternoon Aladin.
I enter the formula, adjust the date to "mm/dd/yy", press enter and get the following message:
"The value you entered is not valid. A user has restricted values that can be entered into this cell." Retry, cancel help....

 

[teamplez]

Was reading another post about the exact error. found it to be a data validation issue. "reset" the data validation. It works now. Thanks so much for your help!

 

[teamplez]

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?