DATEVALUE Error

Pupishi

New Member
Joined
Oct 27, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.

Screenshot 2021-03-30 184904.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Pupishi

New Member
Joined
Oct 27, 2020
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Here is the screenshot with the column names for better reference. Thank you for the advice.

Screenshot 2021-03-30 185436.png


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

Well-known Member
Joined
Feb 15, 2002
Messages
4,650
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Format to your preference; see F6:F7.

Date and Time.xlsm
BCDEFGHI
6Mar 3, 2021 11:12:37 AM442583-Mar-21B6 is a number Date and Time
7Mar 3, 2021 11:12:374425844258442583-Mar-21B7 is text
17a
Cell Formulas
RangeFormula
C6C6=INT(B6)
F6:F7F6=C6
C7C7=DATEVALUE(LEFT(B7,11))
D7D7=DATEVALUE(B7)
E7E7=--MID(B7,1,12)


Date and Time.xlsm
ABC
6number Mar 3, 2021 11:12:37 AMMarch 3, 2021
7TextMar 3, 2021 11:12:37March 3, 2021
8
17a
Cell Formulas
RangeFormula
C6C6=INT(B6)
C7C7=DATEVALUE(LEFT(B7,11))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,311
Messages
5,635,482
Members
416,859
Latest member
GowthamiSita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top