# Converting Twitter date format to standard Excel date format to use with WEEKDAY function

1. ## Converting Twitter date format to standard Excel date format to use with WEEKDAY function

Hello Excel prodigies,

I've been trying to use the WEEKDAY function on a series of tweets I managed to get into Excel, because I want to be able to see the corresponding day of the week for each tweet. However, it seems Excel cannot properly recognize this format 2012-12-16T21:08:09Z as a proper input in the "serial number" field of the WEEKDAY function so I guess I would have to somehow manage to transform it into 16/12/2012 format. Can any Excel expert teach me how to do this the simplest way possible? I attached what kind of table I need.

Gratefully yours
D

2. ## Re: Converting Twitter date format to standard Excel date format to use with WEEKDAY funct

To extract the date.
``Please Login or Register  to view this content.``

3. ## Re: Converting Twitter date format to standard Excel date format to use with WEEKDAY funct

And in fact you can just use Nories's LEFT(C6,10) directly in WEEKDAY function, so if Monday = 1 you can use

=WEEKDAY(LEFT(C6,10),2)

4. ## Re: Converting Twitter date format to standard Excel date format to use with WEEKDAY funct

@Norie

Thank you very much for the quick reply. It works wonders. These functions and their combinations are so cool.

If you don't mind, I would like to ask you how to apply your formula to get results in the form of a range, say D5:D50, paralleling C5:C50. What I tried is selected the empty cells D5:D50, wrote the function putting C5:C50 as input fields, however the output that I get is just the transformation C5 -> D5, the rest remain empty. I know it's probably pretty basic, but what am I doing wrong here?

Again, thank you!
D

5. Put the formula in the first cell of the column.

Then goto the bottom right of the cell with the cursor.

Now you should be able to drag down as far as needed, or double click to fill down to the end of the data.

6. ## Re: Converting Twitter date format to standard Excel date format to use with WEEKDAY funct

Thank you so much. Now it works.

There are currently 1 users browsing this thread. (0 members and 1 guests)