Formatting as dates that will group in a pivot table

[jemwig]

Hi. I am having trouble with date formats. I have a column of dates but some are formatted as dates and others are not see below. I need to format the whole column of values to dates that can then be used in a pivot table and will group by month. I have tried several methods but none seem to work

Current Contract End Date

10/30/28

2008-11-26​

06/20/28

2001-10-25​

2001-01-26​

02/19/22

08/19/27

04/24/25

06/18/27

04/23/28

2002-06-28​

2012-07-25​

2002-02-23​

06/26/22

12/22/27

2009-04-20​

2006-02-17​

07/13/24

12/22/21

11/21/22

01/29/21

01/14/28

04/18/18

2009-07-26​

2005-01-23​

05/20/23

 

[awoohaw]

=DATE(2000+TextAfter(A2,"/",2),Textbefore(A2,"/",1),textafter(textbefore(A2,"/",2),"/"))

Book2
	A	B
1
2	05/20/23	2023-05-20
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATE(2000+TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/"))

I think.

 

[awoohaw]

=DATE(2000+TextAfter(A2,"/",2),Textbefore(A2,"/",1),textafter(textbefore(A2,"/",2),"/"))

Book2
	A	B
1
2	05/20/23	2023-05-20
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATE(2000+TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/"))

I think.

My default date formatting may be different from yours, but you can update that.
The above may not work in 2010, but will in 365.

 

[awoohaw]

try either of these, one is for 365, other is for 2010:

Book2
	A	B	C
1			Excel:
2	05/20/23	05/20/2023	365
3	05/20/23	05/20/2023	2010
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATE(2000+TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/"))
B3	B3	=DATE(MID(A3,FIND("/",A3,FIND("/",A3)+1)+1,2)+2000,LEFT(A3,2),MID(A3,FIND("/",A3)+1,2))

 

[jemwig]

try either of these, one is for 365, other is for 2010:

Book2
	A	B	C
1			Excel:
2	05/20/23	05/20/2023	365
3	05/20/23	05/20/2023	2010
Sheet1

Cell Formulas
Range	Formula
B2	B2	=DATE(2000+TEXTAFTER(A2,"/",2),TEXTBEFORE(A2,"/",1),TEXTAFTER(TEXTBEFORE(A2,"/",2),"/"))
B3	B3	=DATE(MID(A3,FIND("/",A3,FIND("/",A3)+1)+1,2)+2000,LEFT(A3,2),MID(A3,FIND("/",A3)+1,2))

Hi Thank you so much! The 365 one worked to transform the data to date format. But now I need to be able to group the data so I can get a a total number for each month. I.e. if there are 4 entries in the column with dates that lie in Jan 2024,for instance, then I would like to pull a table with a row for each month and the number of entries in each month so that I can graph it.
Like this
Jan 2024 - 12
Feb 2024 - 8
Mar 2024 - 18

 

[awoohaw]

Hi Thank you so much! The 365 one worked to transform the data to date format. But now I need to be able to group the data so I can get a a total number for each month. I.e. if there are 4 entries in the column with dates that lie in Jan 2024,for instance, then I would like to pull a table with a row for each month and the number of entries in each month so that I can graph it.
Like this
Jan 2024 - 12
Feb 2024 - 8
Mar 2024 - 18

your pivot chart wizard should do that with built in functionality. Just drag the date into the wizard and you'll see other options around dates show up.

 

[jemwig]

your pivot chart wizard should do that with built in functionality. Just drag the date into the wizard and you'll see other options around dates show up.

It shows the grouping options but they are split by year

 

[awoohaw]

It shows the grouping options but they are split by year

i'm not sure why you can't get it to just do months, like this:

mr excel questions 34.xlsm
	D	E	F	G	H	I	J	K	L	M	N
1	TheDate	Items Sold	Amount
2	2021-01-29	8	700
3	2028-01-14	6	900		FORMULA CROSSTAB				PIVOT TABLE
4	2022-02-19	7	800
5	2018-04-18	9	800		Month	Count Sales	Dollar Sales		Month	Count Sales	Dollar Sales
6	2025-04-24	6	600		Jan	14	1600		Jan	14	$1,600
7	2028-04-23	6	500		Feb	7	800		Feb	7	$800
8	2023-05-20	7	700		Apr	21	1900		Apr	21	$1,900
9	2022-06-26	6	500		May	7	700		May	7	$700
10	2027-06-18	9	900		Jun	25	2200		Jun	25	$2,200
11	2028-06-20	10	800		Jul	9	900		Jul	9	$900
12	2024-07-13	9	900		Aug	78	7500		Aug	78	$7,500
13	2002-08-25	9	500		Oct	10	600		Oct	10	$600
14	2002-08-26	10	500		Nov	6	700		Nov	6	$700
15	2003-08-23	5	800		Dec	16	1200		Dec	16	$1,200
16	2003-08-28	6	900						Grand Total	193	$18,100
17	2006-08-23	6	700
18	2007-08-17	7	600
19	2009-08-26	10	600
20	2010-08-20	7	500
21	2010-08-26	6	700
22	2013-08-25	6	800
23	2027-08-19	6	900
24	2028-10-30	10	600
25	2022-11-21	6	700
26	2021-12-22	7	600
27	2027-12-22	9	600
jemwig

Cell Formulas
Range	Formula
H6:H15	H6	=UNIQUE(SORT(DATE(0,MONTH(D2:D27),1)))
I6:I15	I6	=SUM((--(MONTH(H6)=MONTH($D$2:$D$27)))*($E$2:$E$27))
J6:J15	J6	=SUM((--(MONTH(H6)=MONTH(D$2:D$27)))*($F$2:$F$27))
D2:D27	D2	=IF(MID(B2,3,1)="/",DATE(2000+TEXTAFTER(B2,"/",2),TEXTBEFORE(B2,"/",1),TEXTAFTER(TEXTBEFORE(B2,"/",2),"/")), DATE(LEFT(B2,4),LEFT(TEXTBEFORE(B2,"-",2),2),LEFT(TEXTAFTER(B2,"-",2),2)))
Dynamic array formulas.