LinkBack URL
About LinkBacks
I downloaded an excel CSV file and the dates apeared as 2.01402E+16. I tried formatting it to number but got 20140221000000000. Can someone please assist inremoving the zeros at the end to make it 20140221?
Many thanks.
I downloaded an excel CSV file and the dates apeared as 2.01402E+16. I tried formatting it to number but got 20140221000000000. Can someone please assist inremoving the zeros at the end to make it 20140221?
Many thanks.
Assuming date is in A1
=left(A1,8)
Click * below if this answer helped
Maybe this
=A1/1000000000
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
How do you determine that the value should end in 21?
If the value is in A1 use =left(A1,8) to get the correct number of digits for a date. If this value is in B1 then you can use the following to get the date serial number which you can format to the date presentation that you want.
Formula:
=DATE(LEFT(B1,4),MID(B1,5,2),RIGHT(B1,2))
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Thanks Guys. I used the last formula in the last post and it worked.
Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks