Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Oct 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show serial values for dates

    I am using Excel 2007 in Windows 7.

    I am working with an exported file from Outlook with the oblect of extracting total time based on the "show time as" field.

    All data is in text format so I used the trim function to trim it to a date number.

    When I highlighted a column of dates in the dd/mm/yyyy format and tried to change them to a general or a number format to get the serial values the first cell changed but the rest of the column stayed in the original format even though when I checked by highlighting each cell it appears to be formatted the same.

    I tried the format painter from cell to cell and from the correct cell to the column but all still display as dd/mm/yyyy

    Anyone see what I am missing here?

    Also, I posted a question in the Outlook forum looking for a program that would mine the data directly in the calendar application but no one had any advise. Has anyone in this group come across such an animal?

    Thanks for any help.

    Larry

  2. #2
    2 Star Lounger
    Join Date
    Mar 2010
    Location
    Tampa, FL, USA
    Posts
    114
    Thanks
    11
    Thanked 10 Times in 9 Posts
    Quote Originally Posted by lb14100 View Post
    ...All data is in text format so I used the trim function to trim it to a date number.
    I don't believe you can convert dates in text format using trim. You will need to parse the strings to extract the dd, mm and yyyy substrings, convert to values, and plug into the Date function like:
    =DATE(VALUE("yyyy"),VALUE("mm"),VALUE("dd"))

    Then the cell can be formatted with General or Number formatting to reveal the serial date number.
    PJ in FL

  3. #3
    New Lounger
    Join Date
    Oct 2008
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PJ,

    I notice that there is a mechanism on teh forums that tracks something called thank You's. Not sure if this one registers but Thanks very much! It's been years since I did any real work in Excel so my recall is REALY slow to say nothing of all teh new toys and layout.

    Just using the Value function on the text cell gives me what I was looking for.

    Thanks Again.

    Larry

Posting Permissions

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