The formula to calculate YEAR & MONTH

N

ngocanh87

Board Regular
Joined
Mar 16, 2016
Messages
85
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hello all, I have file excel Date A Column, i want B col as condition below. thank all
-2016 ,2018,2019 ,2020 include the whole year
-In 2021 compounded quarterly (1~3.4~6.7~9,10~12)
-The year 2022 by month

Book1.xlsx
A
1
2
3Ngày nhập kho
4Input date
5登録日
631/08/2021
731/08/2021
831/08/2021
931/08/2021
1031/08/2021
1131/08/2021
1206/11/2021
1306/11/2021
1406/11/2021
1506/11/2021
1606/11/2021
1706/11/2021
1806/11/2021
1906/11/2021
2009/11/2021
2109/11/2021
2209/11/2021
2309/11/2021
2406/09/2021
2506/09/2021
2606/09/2021
2706/09/2021
2806/09/2021
2906/09/2021
3006/09/2021
3106/09/2021
3206/09/2021
3306/09/2021
3406/09/2021
3506/09/2021
3606/09/2021
3706/09/2021
3806/09/2021
3906/09/2021
4006/09/2021
4106/09/2021
4206/09/2021
4306/09/2021
4406/09/2021
4531/08/2021
4631/08/2021
4731/08/2021
4808/11/2021
4908/11/2021
5008/11/2021
5108/11/2021
5208/11/2021
5308/11/2021
5408/11/2021
5508/11/2021
5622/07/2021
5722/07/2021
5822/07/2021
5922/07/2021
6022/07/2021
6122/07/2021
6222/07/2021
6322/07/2021
6422/07/2021
6522/07/2021
6622/07/2021
6722/07/2021
6823/07/2021
6923/07/2021
7023/07/2021
7123/07/2021
7223/07/2021
7323/07/2021
7423/07/2021
7523/07/2021
7623/07/2021
7723/07/2021
7823/07/2021
7923/07/2021
8023/07/2021
8123/07/2021
8223/07/2021
8323/07/2021
8408/07/2021
8508/07/2021
8608/07/2021
8708/07/2021
8808/07/2021
8908/07/2021
9008/07/2021
9108/07/2021
9208/07/2021
9308/07/2021
9408/07/2021
9508/07/2021
9608/07/2021
9708/07/2021
9808/07/2021
9908/07/2021
10008/07/2021
10108/07/2021
10208/07/2021
10309/07/2021
10409/07/2021
10509/07/2021
10609/07/2021
10709/07/2021
10809/07/2021
10909/07/2021
11009/07/2021
11109/07/2021
11209/07/2021
11309/07/2021
11409/07/2021
11509/07/2021
11609/07/2021
11709/07/2021
11816/07/2021
11907/05/2022
12007/05/2022
12107/05/2022
12207/05/2022
12307/05/2022
12407/05/2022
12507/05/2022
12607/05/2022
12707/05/2022
12807/05/2022
12907/05/2022
13007/05/2022
13107/05/2022
13207/05/2022
13307/05/2022
13407/05/2022
13507/05/2022
13606/05/2022
13706/05/2022
13806/05/2022
13906/05/2022
14006/05/2022
14106/05/2022
14206/05/2022
14306/05/2022
14406/05/2022
14506/05/2022
14607/05/2022
14707/05/2022
14807/05/2022
14907/05/2022
15007/05/2022
15107/05/2022
15207/05/2022
15307/05/2022
15407/05/2022
15507/05/2022
15607/05/2022
15707/05/2022
15807/05/2022
15907/05/2022
16002/08/2021
16102/08/2021
16202/08/2021
16302/08/2021
16402/08/2021
16502/08/2021
16602/08/2021
16702/08/2021
16802/08/2021
16902/08/2021
17002/08/2021
17102/08/2021
17202/08/2021
17302/08/2021
17402/08/2021
17502/08/2021
17602/08/2021
17702/08/2021
17802/08/2021
17902/08/2021
18002/08/2021
18102/08/2021
18202/08/2021
18302/08/2021
18402/08/2021
18502/08/2021
18602/08/2021
18702/08/2021
18802/08/2021
18902/08/2021
19002/08/2021
19102/08/2021
19202/08/2021
19302/08/2021
19402/08/2021
19502/08/2021
19603/08/2021
19703/08/2021
19803/08/2021
19903/08/2021
20003/08/2021
20103/08/2021
20203/08/2021
20303/08/2021
20403/08/2021
20503/08/2021
20603/08/2021
20703/08/2021
20803/08/2021
20903/08/2021
21011/10/2021
21111/10/2021
21213/10/2021
21313/10/2021
21413/10/2021
21513/10/2021
21613/10/2021
21713/10/2021
21813/10/2021
21904/05/2022
22004/05/2022
22104/05/2022
22204/05/2022
22304/05/2022
22404/05/2022
22504/05/2022
22604/05/2022
22705/05/2022
22805/05/2022
22905/05/2022
23005/05/2022
23105/05/2022
23205/05/2022
23305/05/2022
23405/05/2022
23505/05/2022
23605/05/2022
23705/05/2022
23805/05/2022
BẢN Excel khi đã thao tác
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
I explant more:
-2016 ,2018,2019 ,2020 include the whole year
-In 2021 compounded quarterly (1~3.4~6.7~9,10~12)
-The year 2022 by month

A (dd/MM/yyyy)B
27/02/20162016
31/08/2021 7~9/2021
15/10/202110~12/2021
07/05/202205/2022
 
Upvote 0
If you have columns with values then a pivot table will let you summarise by yeas & quarters.

I am sure others can come up with better formulas but this might work for you based on your example:

20220706 Date Different string formats ngocanh87.xlsx
AB
1
2
3
4
5A (dd/MM/yyyy)Requirement
627/02/20162016
731/08/20217~9/2021
815/10/202110~12/2021
97/05/202205/2022
2nd Sample
Cell Formulas
RangeFormula
B6:B9B6=IF(YEAR(A6)<2021,TRIM(YEAR(A6)),IF(YEAR(A6)=2022,TEXT(A6,"mm/yyyy"),CHOOSE(INT(MONTH(A6)/3)+1,"1~3/","4~6/","7~9/","10~12/")&YEAR(A6)))
 
Upvote 0
Sorry because export from software, it will as: '31/08/2021 (as Text). Can you have me this case
1657091565089.png
 
Upvote 0
The formula still works if it is text, there has to be more going on.
In B6 can you put =len(B6) and tell me what it gives you. You should get 10 but I suspect you will get more than that.
If you get 11, then in C6 enter =CODE(RIGHT(B6,1)) and tell me what you get.
 
Upvote 0
Sorry B6 or A6, I think A6 ?
=len(A6) get 10
=CODE(RIGHT(A6,1)) get 49
1657092955404.png
 
Upvote 0
Hmmm, is dd/mm/yyyy your regional date format.

PS: Can you also update your account details to show what version of excel you are using.
 
Upvote 0
.. and can you give us that sample data with XL2BB so we can test with the same data as you?
 
Upvote 0
Dear, this is sample data, but when i use XL2BB it auto remove '

Ngày nhập kho
Input date
登録日
31/08/2021
31/08/2021
31/08/2021
31/08/2021
31/08/2021
31/08/2021
07/05/2022
06/05/2022
06/05/2022
06/05/2022
06/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
05/05/2022
26/04/2022
26/04/2022
26/04/2022
26/04/2022
26/04/2022
26/04/2022
26/04/2022
26/04/2022
28/04/2022
28/04/2022
28/04/2022
28/04/2022
28/04/2022
28/04/2022
28/04/2022
04/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
05/11/2020
23/12/2020
18/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
22/08/2020
18/08/2020
26/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
30/09/2020
09/07/2020
09/07/2020
09/07/2020
09/07/2020
09/07/2020
09/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
10/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
11/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
13/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
14/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
15/07/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
29/06/2020
30/06/2020
30/06/2020
30/06/2020
30/06/2020
30/06/2020
30/06/2020
30/06/2020
30/06/2020
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
27/02/2016
04/05/2022
04/05/2022
 
Upvote 0
In some common cases, in Vietnam (and maybe from europe) , data imported from other region, some cells with dd/mm/yyyy, others with mm/dd/yyyy

With A6 is date- string, try in B6 to convert:
=IFERROR(A6+0,DATE(RIGHT(A6,4)+0,LEFT(A6,2)+0,MID(A6,4,2)+0))

But, with month<10, we have no way to test which format it is.

@ngocanh87, in next cell B6, try to input month(A6) then drag down.
What result?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
489
Asbestos_Jen
Asbestos_Jen
K
  • Solved
Evaluate date range and return the corresponding month and year
Replies
1
Views
146
Scott Huish
Scott Huish
S
  • Solved
Count of active employees between dates.
Replies
3
Views
667
Fluff
Fluff
S
  • Solved
How to Highlight Month-end dates
Replies
3
Views
611
kevin9999
K
J
  • Solved
Excel Formula to highlight most recent record
Replies
3
Views
405
etaf
etaf

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

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