Convert dd/mm/yyyy hh:mm

[Sundance_Kid]

Hi,
I have a file I am downloading with multiple rows and there is a date column in the format dd/mm/yyyy hh:mm

I want to convert it into two additional views:
1. Just a month & year view
2. A date, month and year view

Any idea on the best approach to change it?

Thanks

 

[DRSteele]

Check out some videos on YouTube to see how to manipulate date and time formats, starting with this one.

 

[Dave Patton]

format to your preference

Dates Time .xlsm
	A	B	C
1
2	16-Feb-2023 14:29	Feb2023	16-Feb-23
3
3c

Cell Formulas
Range	Formula
A2	A2	=NOW()
B2	B2	=TEXT(A2,"mmmyyyy")
C2	C2	=INT(A2)

 

[Sundance_Kid]

format to your preference

Dates Time .xlsm
	A	B	C
1
2	16-Feb-2023 14:29	Feb2023	16-Feb-23
3
3c

Cell Formulas
Range	Formula
A2	A2	=NOW()
B2	B2	=TEXT(A2,"mmmyyyy")
C2	C2	=INT(A2)

Many thanks, that worked

 

[Sundance_Kid]

Just one thing, when I use the formula in B2 and do a pivot table after, it does not seem possible to sort the dates from oldest to newest, is that because it is a text?
Thanks

 

[Dave Patton]

consider column D

Dates Time.xlsm
	A	B	C	D
1
2	10-Jan-2023 14:38	Jan2023	10-Jan-23	202301
3	05-Feb-2023 14:38	Feb2023	5-Feb-23	202302
4	02-Mar-2023 00:00	Mar2023	2-Mar-23	202303
5
3c

Cell Formulas
Range	Formula
B2:B4	B2	=TEXT(A2,"mmmyyyy")
C2:C4	C2	=INT(A2)
D2:D4	D2	=TEXT(A2,"yyyymm")

 

[Sundance_Kid]

Thank you D2 worked.

Its not necessary but is it possible to have D2 in date format - like 2022-Feb instead of 202202?

 

[Dave Patton]

" Its not necessary but is it possible to have D2 in date format - like 2022-Feb instead of 202202?"

Did you try it?
Yes, but then the sort would not work on that column.