DATEVALUE Error

[Pupishi]

Hello,

I am trying to convert a date with a timestamp into a date value.
However, although I tried multiple approaches, like using INT(A2), the value is still not recognized as a date value.
And the weird part is that some of the original values are working as a date value when I copy/paste to Google Sheet (meaning, the datevalue formula works in Google Sheet for some values), so I don't think it's the system date configuration issue...
If there is any solution that might be able to fix this, I would greatly appreciate it if you could kindly share.

Thank you so much.

 

[Pupishi]

Here is the screenshot with the column names for better reference. Thank you for the advice.

Hello,

I am trying to convert a date with a timestamp into a date value.
However, although I tried multiple approaches, like using INT(A2), the value is still not recognized as a date value.
And the weird part is that some of the original values are working as a date value when I copy/paste to Google Sheet (meaning, the datevalue formula works in Google Sheet for some values), so I don't think it's the system date configuration issue...
If there is any solution that might be able to fix this, I would greatly appreciate it if you could kindly share.

Thank you so much.

View attachment 35590

 

[Dave Patton]

Format to your preference; see F6:F7.

Date and Time.xlsm
	B	C	D	E	F	G	H	I
6	Mar 3, 2021 11:12:37 AM	44258			3-Mar-21	B6 is a number Date and Time
7	Mar 3, 2021 11:12:37	44258	44258	44258	3-Mar-21	B7 is text
17a

Cell Formulas
Range	Formula
C6	C6	=INT(B6)
F6:F7	F6	=C6
C7	C7	=DATEVALUE(LEFT(B7,11))
D7	D7	=DATEVALUE(B7)
E7	E7	=--MID(B7,1,12)

Date and Time.xlsm
	A	B	C
6	number	Mar 3, 2021 11:12:37 AM	March 3, 2021
7	Text	Mar 3, 2021 11:12:37	March 3, 2021
8
17a

Cell Formulas
Range	Formula
C6	C6	=INT(B6)
C7	C7	=DATEVALUE(LEFT(B7,11))