Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    date format (2000)

    <P ID="edit" class=small>Edited by jlkirk on 02-Apr-02 21:33.</P>I have a spreadsheet I download from an external source that is extremely large. Within the spreadsheet, there are two columns that contain dates, one a beginning date and the other an ending date. These two dates appear on each row of the spreadsheet and number in the thousands.

    My problem is that the format of the date is "YYYY/MM/DD/H". I would like to change the format so that I can compute the number of days between the two "dates" as they are downloaded, but can't seem to get it to work.

    Any help out there?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: date format (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> jlkirk

    There are multiple ways to get to what you are trying.

    1) After the data is received into MS-Excel, go to the column to the right of where the End date is, say that the End dates are in Column B, you would need to insert a column C. Then use the formula ="Begin Date" - "End Date" and format that as "yyyy-m-d" or the way you want it.

    2) During the download process you can specify the data as being dates and give it the format that you want.

    These <<< These two dates appear on each row of the spreadsheet and number in the thousands >>> are Julian Dates and what the number means is that its been that many days since January 1st 1900. In Excel you can also set the year to be 1904 under Tools|Options. So today's Julian date number would be 37,348 and if you count very carefully you will get that many days since 1/1/1900.

    HTH

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    It appears that the problem is the trailing "/H". Code such as:

    Range("A:A").NumberFormat= "yyyy/dd/mm"

    will realise the date format. Don't have much time, but if you strip the trailing "/H", you will realise your values. HTH
    Gre

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    If the cells really contain dates, then you should be able to subtract the begin date from the end date and get the number of days between the two (format the cell containg the formula as a number).

    However, it sounds like the cells really contain text not date serial numbers.

    It is tough to tell what you really have without seeing exactly what you have. Could you make a copy of the workbook and delete everything except 30 or 40 rows of the two columns contaning the dates and upload that workbook attached to a reply to this message?
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    Legare,
    Attached is a sample of the two columns. I should have mentioned the file is originally downloaded as a ".csv" file (whatever that is). I then convert it to an .xls file.
    Could that have anything to do with my problem, and is there any way ti fix it?
    Thanks,
    Jeff
    Attached Files Attached Files

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: date format (2000)

    Hi Jeff,
    THe columns appear to be text as Legare suspected. Attached is a simple way of getting round this.
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    Rory,
    It sure looks like it! Thanks! BTW, how did you do it? That is, what does that function do that is different?
    Thanks again!
    Jeff

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: date format (2000)

    The two interim columns simply take the first 10 characters from the left of the original columns (i.e. the yyyy/mm/dd bit) which Excel can translate happily into a date and then the days column simply subtracts one from the other - that works because in Excel 1 = 1 day. If you wanted months or something else you'd need to use a function like datedif.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    Excellent!
    One more question-is there a way I can take the 2 columns as they are downloaded and then create two additional columns for each original column-one showing the day on/off, and the second showing the hour?
    Thanks,
    Jeff

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    The attached sheet converts your text values to an Excel Date/Time serial number and displays the result in the same format you had. It then shows the difference to two decimal places. You can change the format on the new columns to meet your requirements.
    Attached Files Attached Files
    Legare Coleman

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: date format (2000)

    Thanks, Legare. As usual, your solution works like a charm!
    Jeff

Posting Permissions

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