The formula to calculate YEAR & MONTH

[ngocanh87]

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
3	Ngày nhập kho
4	Input date
5	登録日
6	31/08/2021
7	31/08/2021
8	31/08/2021
9	31/08/2021
10	31/08/2021
11	31/08/2021
12	06/11/2021
13	06/11/2021
14	06/11/2021
15	06/11/2021
16	06/11/2021
17	06/11/2021
18	06/11/2021
19	06/11/2021
20	09/11/2021
21	09/11/2021
22	09/11/2021
23	09/11/2021
24	06/09/2021
25	06/09/2021
26	06/09/2021
27	06/09/2021
28	06/09/2021
29	06/09/2021
30	06/09/2021
31	06/09/2021
32	06/09/2021
33	06/09/2021
34	06/09/2021
35	06/09/2021
36	06/09/2021
37	06/09/2021
38	06/09/2021
39	06/09/2021
40	06/09/2021
41	06/09/2021
42	06/09/2021
43	06/09/2021
44	06/09/2021
45	31/08/2021
46	31/08/2021
47	31/08/2021
48	08/11/2021
49	08/11/2021
50	08/11/2021
51	08/11/2021
52	08/11/2021
53	08/11/2021
54	08/11/2021
55	08/11/2021
56	22/07/2021
57	22/07/2021
58	22/07/2021
59	22/07/2021
60	22/07/2021
61	22/07/2021
62	22/07/2021
63	22/07/2021
64	22/07/2021
65	22/07/2021
66	22/07/2021
67	22/07/2021
68	23/07/2021
69	23/07/2021
70	23/07/2021
71	23/07/2021
72	23/07/2021
73	23/07/2021
74	23/07/2021
75	23/07/2021
76	23/07/2021
77	23/07/2021
78	23/07/2021
79	23/07/2021
80	23/07/2021
81	23/07/2021
82	23/07/2021
83	23/07/2021
84	08/07/2021
85	08/07/2021
86	08/07/2021
87	08/07/2021
88	08/07/2021
89	08/07/2021
90	08/07/2021
91	08/07/2021
92	08/07/2021
93	08/07/2021
94	08/07/2021
95	08/07/2021
96	08/07/2021
97	08/07/2021
98	08/07/2021
99	08/07/2021
100	08/07/2021
101	08/07/2021
102	08/07/2021
103	09/07/2021
104	09/07/2021
105	09/07/2021
106	09/07/2021
107	09/07/2021
108	09/07/2021
109	09/07/2021
110	09/07/2021
111	09/07/2021
112	09/07/2021
113	09/07/2021
114	09/07/2021
115	09/07/2021
116	09/07/2021
117	09/07/2021
118	16/07/2021
119	07/05/2022
120	07/05/2022
121	07/05/2022
122	07/05/2022
123	07/05/2022
124	07/05/2022
125	07/05/2022
126	07/05/2022
127	07/05/2022
128	07/05/2022
129	07/05/2022
130	07/05/2022
131	07/05/2022
132	07/05/2022
133	07/05/2022
134	07/05/2022
135	07/05/2022
136	06/05/2022
137	06/05/2022
138	06/05/2022
139	06/05/2022
140	06/05/2022
141	06/05/2022
142	06/05/2022
143	06/05/2022
144	06/05/2022
145	06/05/2022
146	07/05/2022
147	07/05/2022
148	07/05/2022
149	07/05/2022
150	07/05/2022
151	07/05/2022
152	07/05/2022
153	07/05/2022
154	07/05/2022
155	07/05/2022
156	07/05/2022
157	07/05/2022
158	07/05/2022
159	07/05/2022
160	02/08/2021
161	02/08/2021
162	02/08/2021
163	02/08/2021
164	02/08/2021
165	02/08/2021
166	02/08/2021
167	02/08/2021
168	02/08/2021
169	02/08/2021
170	02/08/2021
171	02/08/2021
172	02/08/2021
173	02/08/2021
174	02/08/2021
175	02/08/2021
176	02/08/2021
177	02/08/2021
178	02/08/2021
179	02/08/2021
180	02/08/2021
181	02/08/2021
182	02/08/2021
183	02/08/2021
184	02/08/2021
185	02/08/2021
186	02/08/2021
187	02/08/2021
188	02/08/2021
189	02/08/2021
190	02/08/2021
191	02/08/2021
192	02/08/2021
193	02/08/2021
194	02/08/2021
195	02/08/2021
196	03/08/2021
197	03/08/2021
198	03/08/2021
199	03/08/2021
200	03/08/2021
201	03/08/2021
202	03/08/2021
203	03/08/2021
204	03/08/2021
205	03/08/2021
206	03/08/2021
207	03/08/2021
208	03/08/2021
209	03/08/2021
210	11/10/2021
211	11/10/2021
212	13/10/2021
213	13/10/2021
214	13/10/2021
215	13/10/2021
216	13/10/2021
217	13/10/2021
218	13/10/2021
219	04/05/2022
220	04/05/2022
221	04/05/2022
222	04/05/2022
223	04/05/2022
224	04/05/2022
225	04/05/2022
226	04/05/2022
227	05/05/2022
228	05/05/2022
229	05/05/2022
230	05/05/2022
231	05/05/2022
232	05/05/2022
233	05/05/2022
234	05/05/2022
235	05/05/2022
236	05/05/2022
237	05/05/2022
238	05/05/2022
BẢN Excel khi đã thao tác

 

[Peter_SSs]

when i use XL2BB it auto remove '

Ok, you are right. What happens if you try these formulas?

22 07 06.xlsm
	A	B	C
6	27/02/2016	2016	2016
7	31/08/2021	7~9/2021	7~9/2021
8	15/10/2021	10~12/2021	10~12/2021
9	07/05/2022	05/2022	05/2022
Time periods

Cell Formulas
Range	Formula
B6:B9	B6	=IF(YEAR(A6)<2021,YEAR(A6),IF(YEAR(A6)=2021,INT((MONTH(A6)-1)/3)*3+1&"~"&INT((MONTH(A6)-1)/3)*3+1+2&"/"&YEAR(A6),MID(A6,4,7)))
C6:C9	C6	=IF(RIGHT(A6,4)-2021<0,RIGHT(A6,4),IF(RIGHT(A6,4)-2021=0,INT((MID(A6,4,2)-1)/3)*3+1&"~"&INT((MID(A6,4,2)-1)/3)*3+1+2&RIGHT(A6,5),MID(A6,4,7)))

 

[ngocanh87]

i think it correct. Thank you and everyone so much.

 

[Alex Blakenburg]

Just for my own benefit, is your computer set up to have mm/dd/yyyy as the default date format ?

 

[ngocanh87]

yes, my computer set up to have mm/dd/yyyy as the default

 

[Alex Blakenburg]

That explains it. I think the assumption was that your default was dd/mm/yyyy but it not automatically handling the text version seemed inconsistent with that.
Thank you for providing feedback. Glad we could help.

 

[Peter_SSs]

i think it correct.

It is unclear to me. You think what correct?

 

[ngocanh87]

It is unclear to me. You think what correct?

You helped me perfect. that's what i want Thank you and Alex Blakenburg

=IF(RIGHT(A6,4)-2021<0,RIGHT(A6,4),IF(RIGHT(A6,4)-2021=0,INT((MID(A6,4,2)-1)/3)*3+1&"~"&INT((MID(A6,4,2)-1)/3)*3+1+2&RIGHT(A6,5),MID(A6,4,7)))

 

[Peter_SSs]

Thanks for the clarification. It is good to know what worked on your side.