CODEWITHSUNDEEP
exceldateindexing
Princess.Bell
Princess.Bell

Reputation: 373

excel extract 6 digits from number, format as date and match with index match?

I have the following 2 worksheets.

Data:

Column B          Column C          Column D      COLUMN E                                         Column F
329115121601      3291              5555          =Date (MID(B8,9,2),MID (B8,7,2),MID (B8,5,2))    50 <---- Week Number from date

I am trying to extract 6 digits from my number value in Columb B '151216' and want to put this into column E. The 6 digits represent a date which is 15th December 2016.

I am using mid to try and get the number and then date to try and format this as 15/12/2016 - however it's not working. Instead I get 15/12/16.

On my other sheet, 'Home' i am trying to look up the number from column B on sheet 'Data' using an index match (array formula) where the values in column C, D and E or F match.

Home:

Column H      Column I      Column J                   Column K
3291          5555          15/12/2016 or Week 50      {=IFERROR(INDEX(Data!$B:$B,MATCH(1,(Home!$H10=Data!$C:$C)*(Home!$I10=Data!$D:$D)*(IF(Home!$J10<55,Home!$J10,WEEKNUM(Home!$J10))=Data!$F:$F),0)),"No Po Found")}

The user should be able to enter either week number or a specific date and get a result.

For some reason, this formula works if the user enters a week number, but not if they enter a specific date. I'm not sure if this is a format issue with the date on sheet 'data' or with the index match formula. Please can someone show me where I am going wrong? Thanks

Upvotes: 0

Views: 270

Answers (2)

Carlos Lucas
Carlos Lucas

Reputation: 88

I think you are doing it well. Just need to format the date. Right click on the cell that contains the date ( 15/12/16 ) then click on cell formatting option and choose the format you prefer.

Upvotes: 0

Steven Harrison
Steven Harrison

Reputation: 31

Excel defaults to 1916 when I input your formula, try adding "20"& to the start of the year parameter:

=Date("20"&MID(B8,9,2),MID(B8,7,2),MID(B8,5,2))

Upvotes: 1

Related Questions