Separate dates from particular month/year

[dvhristov]

Hello everyone!

I’m trying to resolve this issue more than week but unfortunately I couldn’t without help!

I will try my best to explain exactly what I want and for your convenience I’ve made a table with examples.

[table="width: 500, class: grid, align: center"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[td]E[/td]
[/tr]
[tr]
[td]1[/td]
[td]Dates[/td]
[td]Jun-18[/td]
[td]Jul-18[/td]
[td]Aug-18[/td]
[td]Sep-18[/td]
[/tr]
[tr]
[td]2[/td]
[td]14/06/2018[/td]
[td]14/06/2018[/td]
[td]10/07/2018[/td]
[td]08/08/2018[/td]
[td][/td]
[/tr]
[tr]
[td]3[/td]
[td]14/06/2018[/td]
[td]14/06/2018[/td]
[td]12/07/2018[/td]
[td]14/08/2018[/td]
[td][/td][/tr]
[/tr]
[tr]
[td]4[/td]
[td]23/06/2018[/td]
[td]23/06/2018[/td]
[td]25/07/2018[/td]
[td]23/08/2018[/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]10/07/2018[/td]
[td][/td]
[td]25/07/2018[/td]
[td]23/08/2018[/td]
[td][/td]
[/tr]
[tr]
[td]6[/td]
[td]12/07/2018[/td]
[td][/td]
[td][/td]
[td]30/08/2018[/td]
[td][/td]
[/tr]
[tr]
[td]7[/td]
[td]25/07/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]8[/td]
[td]25/07/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]9[/td]
[td]08/08/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]10[/td]
[td]14/08/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]11[/td]
[td]23/08/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]12[/td]
[td]23/08/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]13[/td]
[td]30/08/2018[/td]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[/tr]
[/table]


In column “A” I have list of dates and in row 1 I have list of months and years. I want to separate dates in column “A” and put it in respective month and year from row 1. For example in column “B” I want excel to show me only dates from June 2018; in column “C” only dates from July 2018 and etc. Finally I want this dynamically – when I write a new date in column “A” excel automatically to put it in respective cell under respective month and year.

 

[Peter_SSs]

I am assuming that B1:E1 are also actually dates being the 1st of each month, but formatted to show just mm-yy

If so, try this, copied across and down after adjusting the $200 values to be large enough for any expected data.

Excel Workbook
	A	B	C	D	E
1	Dates	Jun-18	Jul-18	Aug-18	Sep-18
2	14/06/2018	14/06/2018	10/07/2018	8/08/2018
3	14/06/2018	14/06/2018	12/07/2018	14/08/2018
4	23/06/2018	23/06/2018	25/07/2018	23/08/2018
5	10/07/2018		25/07/2018	23/08/2018
6	12/07/2018			30/08/2018
7	25/07/2018
8	25/07/2018
9	8/08/2018
10	14/08/2018
11	23/08/2018
12	23/08/2018
13	30/08/2018
14
List Dates

 

[dvhristov]

Thank you Peter_SSs!

You are right - B1-E1 are actual dates and formatted to show mm-yy.

Unfortunately the formula you suggest doesn't work for me!

In column "E" for example I want to show me only dates from September 2018.

 

[Peter_SSs]

Unfortunately the formula you suggest doesn't work for me!

That doesn't give us much to go on.

You can see that it worked for me, so what does it do for you that is different?
Does it error?
Does it return all blanks?
Does it put dates in the wrong column?
Something else?

You confirmed that B1:E1 are dates but not that those dates were 1-June-18, 1-July-18 etc. Is that the case?

Did you copy/paste my formula from the forum or did you re-type it yourself?

Did you modify the formula in some way to suit a different data layout?

Have you tried the formula in a fresh sheet with exactly this sample data and layout?

What version of Excel are you using?

 

[dvhristov]

Peter_SSs,

I’m sorry for my previous reply! It's my fault! Actually your formula works perfectly! Thank you very much!

 

[Peter_SSs]

Peter_SSs,

I’m sorry for my previous reply! It's my fault! Actually your formula works perfectly! Thank you very much!

Glad you got it sorted out. Thanks for letting us know.