Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula to separate a date from a date/time column.
2017-04-02 12:06 +0000
to
2017-04-02
Solved by T. D. in 9 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
25/04/2018 - 07:28
Hi, It's pleasure to help you today.
Excelchat Expert
25/04/2018 - 07:29
Can you please copy the cell containing date and time.
User
25/04/2018 - 07:29
2017-04-02 12:06 +0000
Excelchat Expert
25/04/2018 - 07:29
Please paste in the preview.
Excelchat Expert
25/04/2018 - 07:31
=text(DATEVALUE(date(mid(A1,1,4),mid(A1,6,2),mid(A1,9,2))),"yyyy-mm-dd")
Excelchat Expert
25/04/2018 - 07:31
I have used mid to extract the string.
Excelchat Expert
25/04/2018 - 07:31
Datevalue is used to convert to date.
Excelchat Expert
25/04/2018 - 07:31
Text is used to convert to the format desired.
User
25/04/2018 - 07:32
If all cells are formatted the same way, is it possible to choose just the first 10 characters?
Excelchat Expert
25/04/2018 - 07:32
yes. I can do that.
Excelchat Expert
25/04/2018 - 07:32
=mid(A1,1,10)
User
25/04/2018 - 07:34
It seems that might be an easier way for this excel rookie to remember.
Excelchat Expert
25/04/2018 - 07:34
Yes. But, that is in text format.
Excelchat Expert
25/04/2018 - 07:34
And you can use it for viewing purpose.
User
25/04/2018 - 07:35
Yes...that worked. I knew I had done this before, but could NOT remember how to do it. Is there a formula to determine how many entries in my spreadsheet each date has?
User
25/04/2018 - 07:35
And does it need to be in datevalue to do it?
Excelchat Expert
25/04/2018 - 07:36
Entries?
Excelchat Expert
25/04/2018 - 07:36
Is it character length?
User
25/04/2018 - 07:37
I mean how many rows of data occurred on the same dates in my spreadsheet. (I'm looking at tweets, each tweet is recorded on a different row and identified by date.)
User
25/04/2018 - 07:37
Looking for a tweets per day.
Excelchat Expert
25/04/2018 - 07:38
It is possible to get the number. But, please consider this as additional question. Pleas visit Got It Pro for new queries as per the policy.
User
25/04/2018 - 07:38
OK.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
