Date by Month

T

Tofik

Board Regular
Joined
Feb 4, 2021
Messages
114
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi guys, how are you? Guys I don't know which one logic I need here to place dates by counties by months.
I need place only earlier date in my Month cell.
Example If I have date: 01.01.2020 , 05.01.2020 , 07.01.2020 then I need only earlier date from January = 01.01.2020 in cell Date 2020

I search on youtube.com and they use count products. or If count max or min.
My problem is I should search date through Country 1 Country 2 Country 3 and Country 4 and take only earlier date from January, February and etc.
country.xlsm
ABCDEFGHIJKL
1Country1Country2Country3Country4Date1No cityMonthsDate 2020 Date 2021
2America Canada Canada America 01.01.20211AmericaJanuaryonly first date from januaryonly first date from january
3Canada America America Canada 05.01.2021February
4America Canada America America 02.03.2020March
5Canada America America America 21.06.2021April
6America America America America 20.07.2021May
7Canada Canada Canada America 06.08.2021June
8America America America America 13.02.2021July
9Canada Canada America Canada 02.04.2020August
10America America Canada Canada 03.04.2021September
11America America America America 01.01.2020October
12America America America America 15.10.2021November
13Canada Canada America America 16.11.2021December
142CanadaJanuary
15February
16March
17April
18May
19June
20July
21August
22September
23October
24November
25December
WELDERS INFO
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Consider the following:
- You need 2 identical formulas, one for America and one for Canada.
- Check your data, because in some cells you have blank spaces to the right of the text.
- This "01.01.2021" that you have in column E is not a date, it is a text, therefore the formula converts it to date to find the smallest. If you decide to change it to date, then the formula should be adjusted. Just as you have it, the formula works.

Dante Amor
ABCDEFGHIJKL
1Country1Country2Country3Country4Date1No cityMonthsDate 2020Date 2021
2AmericaCanadaCanadaAmerica01.01.20211AmericaJanuary01/01/202001/01/2021
3CanadaAmericaAmericaCanada05.01.2021February 13/02/2021
4AmericaCanadaAmericaAmerica02.03.2020March02/03/2020 
5CanadaAmericaAmericaAmerica21.06.2021April02/04/202003/04/2021
6AmericaAmericaAmericaAmerica20.07.2021May  
7CanadaCanadaCanadaAmerica06.08.2021June 21/06/2021
8AmericaAmericaAmericaAmerica13.02.2021July 20/07/2021
9CanadaCanadaAmericaCanada02.04.2020August 06/08/2021
10AmericaAmericaCanadaCanada03.04.2021September  
11AmericaAmericaAmericaAmerica01.01.2020October 15/10/2021
12AmericaAmericaAmericaAmerica15.10.2021November 16/11/2021
13CanadaCanadaAmericaAmerica16.11.2021December  
142CanadaJanuary 01/01/2021
15February  
16March02/03/2020 
17April02/04/202003/04/2021
18May  
19June 21/06/2021
20July  
21August 06/08/2021
22September  
23October  
24November 16/11/2021
25December  
Hoja1
Cell Formulas
RangeFormula
K2:L13K2=IFERROR(SMALL(IF(($A$2:$D$13=$H$2)*(TEXT(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)),"mmmmaaaa")=LOWER($J2)&RIGHT(K$1,4)),(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)))),1),"")
K14:L25K14=IFERROR(SMALL(IF(($A$2:$D$13=$H$14)*(TEXT(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)),"mmmmaaaa")=LOWER($J14)&RIGHT(K$1,4)),(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)))),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
DanteAmor said:
Consider the following:
- You need 2 identical formulas, one for America and one for Canada.
- Check your data, because in some cells you have blank spaces to the right of the text.
- This "01.01.2021" that you have in column E is not a date, it is a text, therefore the formula converts it to date to find the smallest. If you decide to change it to date, then the formula should be adjusted. Just as you have it, the formula works.

Dante Amor
ABCDEFGHIJKL
1Country1Country2Country3Country4Date1No cityMonthsDate 2020Date 2021
2AmericaCanadaCanadaAmerica01.01.20211AmericaJanuary01/01/202001/01/2021
3CanadaAmericaAmericaCanada05.01.2021February 13/02/2021
4AmericaCanadaAmericaAmerica02.03.2020March02/03/2020 
5CanadaAmericaAmericaAmerica21.06.2021April02/04/202003/04/2021
6AmericaAmericaAmericaAmerica20.07.2021May  
7CanadaCanadaCanadaAmerica06.08.2021June 21/06/2021
8AmericaAmericaAmericaAmerica13.02.2021July 20/07/2021
9CanadaCanadaAmericaCanada02.04.2020August 06/08/2021
10AmericaAmericaCanadaCanada03.04.2021September  
11AmericaAmericaAmericaAmerica01.01.2020October 15/10/2021
12AmericaAmericaAmericaAmerica15.10.2021November 16/11/2021
13CanadaCanadaAmericaAmerica16.11.2021December  
142CanadaJanuary 01/01/2021
15February  
16March02/03/2020 
17April02/04/202003/04/2021
18May  
19June 21/06/2021
20July  
21August 06/08/2021
22September  
23October  
24November 16/11/2021
25December  
Hoja1
Cell Formulas
RangeFormula
K2:L13K2=IFERROR(SMALL(IF(($A$2:$D$13=$H$2)*(TEXT(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)),"mmmmaaaa")=LOWER($J2)&RIGHT(K$1,4)),(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)))),1),"")
K14:L25K14=IFERROR(SMALL(IF(($A$2:$D$13=$H$14)*(TEXT(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)),"mmmmaaaa")=LOWER($J14)&RIGHT(K$1,4)),(DATE(RIGHT($E$2:$E$13,4),MID($E$2:$E$13,4,2),LEFT($E$2:$E$13,2)))),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...
I will try. Can I ask you how do you start write this formula , from which part are you start and what are you add step by step ? Because when I saw big formula I decide it is not mine :)
Also if you have time to do that, thanks man !!!
 
Upvote 0
It's easier to explain if you have dates in column E.

Dante Amor
ABCDEFGHIJKL
1Country1Country2Country3Country4Date1No cityMonthsDate 2020Date 2021
2AmericaCanadaCanadaAmerica01/01/20211AmericaJanuary01/01/202001/01/2021
3CanadaAmericaAmericaCanada05/01/2021February 13/02/2021
4AmericaCanadaAmericaAmerica02/03/2020March02/03/2020 
5CanadaAmericaAmericaAmerica21/06/2021April02/04/202003/04/2021
6AmericaAmericaAmericaAmerica20/07/2021May  
7CanadaCanadaCanadaAmerica06/08/2021June 21/06/2021
8AmericaAmericaAmericaAmerica13/02/2021July 20/07/2021
9CanadaCanadaAmericaCanada02/04/2020August 06/08/2021
10AmericaAmericaCanadaCanada03/04/2021September  
11AmericaAmericaAmericaAmerica01/01/2020October 15/10/2021
12AmericaAmericaAmericaAmerica15/10/2021November 16/11/2021
13CanadaCanadaAmericaAmerica16/11/2021December  
142CanadaJanuary 01/01/2021
15February  
16March02/03/2020 
17April02/04/202003/04/2021
18May  
19June 21/06/2021
20July  
21August 06/08/2021
22September  
23October  
24November 16/11/2021
25December  
Hoja1
Cell Formulas
RangeFormula
K2:L13K2=IFERROR(SMALL(IF(($A$2:$D$13=$H$2)*(TEXT($E$2:$E$13,"mmmmaaaa")=LOWER($J2)&RIGHT(K$1,4)),$E$2:$E$13),1),"")
K14:L25K14=IFERROR(SMALL(IF(($A$2:$D$13=$H$14)*(TEXT($E$2:$E$13,"mmmmaaaa")=LOWER($J14)&RIGHT(K$1,4)),$E$2:$E$13),1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


First check which data in the range $A$2:$B$13 is equal to "America" (cell $H$2);
and that also the month and the year of the range $E$2:$E$13 is equal to the lowercase(month) (cell $J2) and the year (4 digits from the right cell K$1).

If the 2 conditions are met, then, of all matches, with the SMALL function and parameter 1 we obtain earlier date.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Make a table from data which updated monthly.
Replies
1
Views
349
adnan1975
A
J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
468
josevaldesv1
J
J
  • Question Question
Dynamic calculations for capacity
Replies
2
Views
86
Jali1992
J
D
  • Question Question
Countif / Sum including a date range
Replies
4
Views
266
djjamesp
D
dezibluenose
  • Solved
Countifs formula not picking up dates, when reference columns show as dates?!
Replies
7
Views
459
Alex Blakenburg
A

Forum statistics

Threads
1,224,814
Messages
6,181,124
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top