Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Convert date field stored as a short text to date that will allow me to figure out number of days between

  1. #1
    New Member
    Join Date
    Apr 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Convert date field stored as a short text to date that will allow me to figure out number of days between

    I have data that I have to copy off of a website in order to get data for my boss. Unfortunately at this time the website does not allow me to export to excel or give me any type of report. Therefore, I have copied the data into an excel spreadsheet.

    One of the columns is a date-time field (8/21/2014 12:56 PM). I believe this field is a short text as it appears as that when I import it into excel. This is where i am having problems. TExt to column doesn't change anything in this field. Changing the format type also doesn't change the information in this field. I want to be able to figure out how many days from today is the date in the column.

    =today() - the date in spreadsheet - when I do this formula I get a #value

    I have tried int(a2), left(a2, 10) and nothing seems to work

    Using Microsoft 2013

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    Hi - does this work to convert the text date?

    =A2+0
    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Apr 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    Nope. #value again

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    How about this?

    =SUBSTITUTE(A2,CHAR(160),"")+0
    [code]your code[/code]

  5. #5
    New Member
    Join Date
    Apr 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    No. It did not work.

    But it did change the font color to gray, but still reads #value

  6. #6
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    If you put this is a spare cell what does it return?

    EDIT:
    =CODE(RIGHT(A2,1))
    [code]your code[/code]

  7. #7
    New Member
    Join Date
    Apr 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    It returned 77. THe date in the field is 8/21/2014 12:56.

  8. #8
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    Can you upload a file with an example date that will not convert to a free file sharing site - like dropbox for example - and share the link here?

    And can you confirm that your regional settings for dates are mm/dd/yyyy?
    [code]your code[/code]

  9. #9
    New Member
    Join Date
    Apr 2013
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between


  10. #10
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,762
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Convert date field stored as a short text to date that will allow me to figure out number of days between

    What are you regional settings for dates? Is it not mm/dd/yyyy?

    Sheet1

     AB
    1ModifiedConverted
    28/21/2014 12:56 PM8/21/2014
    38/12/2014 1:55 PM8/12/2014
    44/28/2014 1:01 PM4/28/2014
    512/3/2014 4:23 PM12/3/2014
    612/3/2014 4:30 PM12/3/2014

    Formeln der Tabelle
    ZelleFormel
    B2=A2+0


    Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
    [code]your code[/code]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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