Extract Date & Time Separately

[dunmore83]

Hi,

In column A I have the following;

2021-05-07 11:31:37 +0800

I need a formula in column B to extract the date only & in column C I need a formula to extract the time only.

Any helpers out there ??

Many thanks!

 

[Marc L]

Hi !​

​

Cell B formula : =DATEVALUE(LEFT(A1,10))​

​

Cell C formula : =TIMEVALUE(MID(A1,12,8))​

​

Format cells in each columns …​

 

[dunmore83]

Thanks that works

Could you also provide a formula I can put in column D which just returns the D/M (i.e. 7/5) & the time to the nearest 15 minutes ?

So for the example above it would be 7/5 11:30pm in column D

 

[Marc L]

​

D/M ok but for the nearest time I have no idea for the moment but I'm sure someone else will help you …​

 

[Dave Patton]

format to your preferences

Date and Time 2021.xlsm
	A	B	C
1	2021-05-07 11:31:37 +0800	7-May-21	11:31:37
2	custom format d"/"m	7/5
3	custom format hh:mm		11:30
4
5	2021-05-07 21:41:37 +0800	7/5	09:45 PM
6
10c

Cell Formulas
Range	Formula
C1	C1	=--MID(A1,12,8)
B1,B5	B1	=--LEFT(A1,10)
B2	B2	=B1
C3	C3	=MROUND(C1,"0:15")
C5	C5	=MROUND(--MID(A5,12,8),"0:15")

 

[dunmore83]

Thanks

Is it possible to refence the cell with this data 2021-05-07 11:31:37 +0800 & return "7/5 11:30" in 1 cell ?

 

[Dave Patton]

What did you try?

=MROUND(--LEFT(A1,19),"0:15") custom format as mentioned previously

 

[dunmore83]

Thanks for your reply

When entering that formula =MROUND(--LEFT(A1,19),"0:15") it just seems to return the time which is 12:15:00 AM

Can I get it to return the date & time like "7/5 12:15" in 1 cell ?

 

[Dave Patton]

Did you apply the custom format d"/"m hh:mm AM/PM to that cell?

Date and Time 2021.xlsm
	A	B
1	2021-05-07 11:31:37 +0800	7/5 11:30 AM
10c

Cell Formulas
Range	Formula
B1	B1	=MROUND(--LEFT(A1,19),"0:15")

 

[dunmore83]

Thanks.

Working now. I wasn't custom formatting the cell correctly. Your custom format works.... d"/"m hh:mm AM/PM

Thanks again !