Matching dates

[Ufoo]

Hello excel gurus,

I am wondering how I can MATCH a month in a date (22/07/2017) with a series of dates in an excel column which are formatted to show month and year (
Jul-17 Aug-17 Sep-17 Oct-17 Nov-17
). Please help. Again thanks a lot

 

[Chihiro]

Upload sample file please. I'm not sure what you want returned via formula.

As general example...
=MATCH(MONTH('lookup Date'),MONTH('lookup range'),0)

Confirmed as array (CTRL + SHIFT + ENTER).

Or
=SUMPRODUCT((MONTH('lookup range')=MONTH('lookup Date'))*ROW(row index))

 

[chirayu]

Do you mean formatting as in cell formatting or that the columns don't actually have dates. They just have MM-YY written.

If its cell formatting then MATCH will work.

IF its text MM-YY then MATCH an exact date to a specific row would not be possible. You could so SUMPRODUCT/ SUMIF but it would display entire month.

 

[SirJB7]

Hi, Ufoo!

Maybe I misunderstood your question but...
Excel store dates as integer numbers starting for 01/01/1900 as 1. That's about storage.
Excel show stored dates according to the cell format, i.e., it maybe dd/mm/yyyy, mm/dd/yyyy, dd-mmm, including dd, mmmm... Taking the above date as example: 01/01/1900, 01/01/1900, 01-Jan, 01, January. That's about displaying.
The useful point is that all the displayed values (let us assume that orginal date is in A1 and cells B1:B5 contain "=$A$1") represent the same date. So the MATCH function will operate flawlessly independently of the cell format but accordingly to the cell value.

In your question, let us say A1 holds 22/07/2017 and B1:B5 holds the posted dates, in C1 you can place this formula:

C1: =MATCH(A1;B1:B5;0)
This for an exact match, if the date displayed as Jul-17 doesn't correspond to day 17 (which is not displayed since format is mmm-yy) it will return a N/A error.

C1: =MATCH(A1;B1:B5;1)
This for a less or equal match, if the date displayed as Jul-17 (B1) is greater than A1 it will return a N/A error.

C1: =MATCH(A1;B1:B5;-1)
This for a greater or equal match, if the date displayed as Nov-17 (B5) is lesser than A1 it will return a N/A error.

Hope it helps.

Regards!

 

[NARAYANK991]

Hi ,

Some more options !

The dates which are formatted to show as mmm-yy can be of two types - the beginning of each month , or the end of each month. If they are neither , then ignore this entire post.

Suppose your date 22/07/2017 is in cell A1.

If your lookup range consists of dates which are the beginning of each month , then you can match using the following formula :

=MATCH(EOMONTH(A1,-1)+1,lookup range,0)

If your lookup range consists of dates which are the end of each month , then you can match using the following formula :

=MATCH(EOMONTH(A1,0),lookup range,0)

Narayan

 

[Ufoo]

Upload sample file please. I'm not sure what you want returned via formula.

As general example...
=MATCH(MONTH('lookup Date'),MONTH('lookup range'),0)

Confirmed as array (CTRL + SHIFT + ENTER).

Or
=SUMPRODUCT((MONTH('lookup range')=MONTH('lookup Date'))*ROW(row index))

. Thanks @Chihiro. In fact, my intention is to match month and year. Let me upload the sheet and explain my problem well.

 

[Ufoo]

Thanks all of you. My problem is in the attached sheet. This is what I want to do: I want to enter data in the daily ledger sheet everyday, and get the data summarized per month&year and per name in the akiba sheet. I hope this is clear. You will see the sumproduct formula that I have tried to create in the akiba sheet

 

[Chihiro]

Try...
=SUMIFS('Daily ledger'!$L$5:$L$8,'Daily ledger'!$K$5:$K$8,$B3,'Daily ledger'!$I$5:$I$8,">="&D$2,'Daily ledger'!$I$5:$I$8,"<="&EOMONTH(D$2,0))

FYI - I'd make ranges referenced in 'Daily ledger' dynamic named range for ease of maintenance, if I were you.

 

[Ufoo]

Try...
=SUMIFS('Daily ledger'!$L$5:$L$8,'Daily ledger'!$K$5:$K$8,$B3,'Daily ledger'!$I$5:$I$8,">="&D$2,'Daily ledger'!$I$5:$I$8,"<="&EOMONTH(D$2,0))

FYI - I'd make ranges referenced in 'Daily ledger' dynamic named range for ease of maintenance, if I were you.

Can't thank you enough @Chihiro