Date assistance formula

[Arts]

Hi all

I am attempting to do an index match formula which is using a date element. The issue I am having is that the date in one sheet is stated as Jun-2024 format type General (below) and on another sheet I have Jun-24 but this is listed as 28/06/2024 but has been formatted as mmm-yy showing as Jun-24. Although they may be a match cosmetically I am assuming the lookup is not working due to them not being the same date as such. Is there a way I can make the dates match within the lookup to get the desired result ?

(no issues within constructing the index match formula itself just within the second match aspect of getting dates to match)

 

[6StringJazzer]

Although they may be a match cosmetically I am assuming the lookup is not working due to them not being the same date as such.

This is correct. MATCH matches on the underlying value of the cell, not what is displayed.

But we can't give you an actual formula without knowing what you need to do. Just for starters what INDEX/MATCH formula did you start with?

 

[Arts]

Thank you for responding

I have pasted in data below, essentially what I am trying to see if there is any spend on a project if it has gone past it's end date so for Project A it should show 0 as it has an end date of Jun-24 but with the data it shows 1. Unfortunately I am matching this against different spreadsheets where one department has entered the data a certain way compared to another.

formula being used is =INDEX(D4:J8,MATCH($N3,$C$4:$C$8,FALSE),MATCH($O3,$D$3:$J$3,FALSE))

Name

End Date

Spend ?

May-2024

Jun-2024

Jul-2024

Aug-2024

Sep-2024

Oct-2024

Nov-2024

Project A

Jun-24

#N/A​

Project F

0​

0​

0​

0​

0​

0​

0​

Project B

Sep-24

Project A

0​

0​

0​

0​

0​

0​

0​

Project C

Mar-26

Project E

0​

0​

0​

0​

0​

0​

0​

Project D

Mar-27

Project A

0​

1​

0​

0​

0​

0​

0​

 

[Arts]

Hi all

Resolved: I used the TEXT function to attain the month and year =TEXT(O3,"mmm")&"-"&YEAR(O3) giving me Jun-24 allowing me to use this within the match.


*marking as resolved to close of post

 

[Peter_SSs]

I used the TEXT function to attain the month and year =TEXT(O3,"mmm")&"-"&YEAR(O3) giving me Jun-24

Surely if O3 contains a date, that formula would return "Jun-2024" not "Jun-24" as started above.

In any case the required text string should be able to be obtained more directly with on of these

If you want "Jun-2024" as per the formula in post 4

=TEXT(O3,"mmm-yyyy")

If you want "Jun-24" as per the stated result in post 4

=TEXT(O3,"mmm-yy")

 

[Arts]

Hi Peter

Thanks for your response

Sorry for the confusion (bad explanation my part). You're right I needed to get to Jun-2024 from Jun-24. and that's what was returned not what I had incorrectly stated. (must have been the semi-excitement of solving something of my own accord...only to be shown a more efficient way )

Thanks for this! =TEXT(O3,"mmm-yyyy") I hadn't thought to include the year this way rather than my longer winded way.