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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
Make a table from data which updated monthly.
Replies
1
Views
193
adnan1975
A
V
  • Solved
Adding only academic months to a date
2 3
Replies
22
Views
697
Dave Patton
D
P
  • Question
Need VBA to automatically copy paste rows based on dates of current month.
2
Replies
11
Views
292
pk970
P
J
  • Question
How to allocate project hours between specific dates using specific daily hour burn rates and including awkward calendar month start/end+holidays
2
Replies
11
Views
1K
felixstraube
F
Sweedler
  • Solved
Weekends in date based formulas
Replies
7
Views
131
Fluff
Fluff

Forum statistics

Threads
1,215,430
Messages
6,124,850
Members
449,194
Latest member
HellScout

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