DATEVALUE Error

Pupishi

New Member
Joined
Oct 27, 2020
Messages
9
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 Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

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
Back
Top