+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    02-06-2013
    Location
    brussels
    MS-Off Ver
    Excel 2010
    Posts
    3

    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
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620

    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.
    If posting code please use code tags, see here.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    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)
    Audere est facere

  4. #4
    Registered User
    Join Date
    02-06-2013
    Location
    brussels
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    @Norie
    @daddylonglegs

    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. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,620
    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. #6
    Registered User
    Join Date
    02-06-2013
    Location
    brussels
    MS-Off Ver
    Excel 2010
    Posts
    3

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

    Thank you so much. Now it works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1