Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Dates, Conversion and Sorting

    Hi all,

    Attached is a cut-down version of a spreadsheet I've made, with some macros. I'm not pretending the macros are the most efficient in the world, but please ignore that! I need some help with dates.

    I know that dates in Excel are really numbers, representing the number of donuts God has eaten since the beginning of the universe. I know that in VB you convert from String dates to Date dates using the CDATE function and the FORMAT function if you need.

    My problem is this:

    I have a "source" worksheet and a "destination" worksheet, which are essentially being compared field by field to check for changes. One column on each sheet is a date column. This date column on both sheets is just text (and needs to be because it comes from an CSV file) i..e when you sort the dates, they are just ordered ALPHANUMERICALLY and not according to the date.

    So in my VB code (function ConvertDates) I convert both the source and destination "text dates" to proper date objects in order that I can compare them properly. I convert the dates and set their format like:

    Code:
       s.Cells(1, CTenderDueDateCol) = FORMAT(CDate(s.Cells(1, CTenderDueDateCol))), "dd/mm/yyyy")
    
       d.Cells(1, CTenderDueDateCol) = FORMAT(CDate(s.Cells(1, CTenderDueDateCol))), "dd/mm/yyyy")
    
      if s.Cells(1, CTenderDueDateCol) > d.Cells(1, CTenderDueDateCol)) then
         etc
      end if
    In line with expectations, my code converts the dates ok and compares them ok.. however, against my expectations, the dates in d.Cells(1, CTenderDueDateCol) and s.Cells(1, CTenderDueDateCol) cells in the worksheets are still just text, so when I sort them, they are still Alphanumeric. I need to sort by date!

    HELP!

    I've tried playing with the formats of the cells in the sheets themselves, but if I select a date format, it makes no difference to the sorting because they are just text..
    Attached Files Attached Files

  2. #2
    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
    That is because your cells are explicitly formatted as text. You need something like:
    Code:
      s.Columns(CTenderDueDateCol).Numberformat = "dd/mm/yyyy"
      d.Columns(CTenderDueDateCol).Numberformat = "dd/mm/yyyy"
       s.Cells(1, CTenderDueDateCol).Value = CDate(s.Cells(1, CTenderDueDateCol).Value)
       d.Cells(1, CTenderDueDateCol).Value = CDate(s.Cells(1, CTenderDueDateCol).Value)
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post

    Smile

    ahh it's just the little things...
    thanks..
    Dom

    p..s I'd tried changing the format of the column manually after the macros had run, but this of course had not effect because text is text..
    Last edited by dom_donald; 2011-02-28 at 08:42.

  4. #4
    2 Star Lounger
    Join Date
    Oct 2007
    Posts
    172
    Thanks
    0
    Thanked 2 Times in 1 Post
    Quote Originally Posted by rory View Post
    That is because your cells are explicitly formatted as text. You need something like:
    Code:
      s.Columns(CTenderDueDateCol).Numberformat = "dd/mm/yyyy"
      d.Columns(CTenderDueDateCol).Numberformat = "dd/mm/yyyy"
       s.Cells(1, CTenderDueDateCol).Value = CDate(s.Cells(1, CTenderDueDateCol).Value)
       d.Cells(1, CTenderDueDateCol).Value = CDate(s.Cells(1, CTenderDueDateCol).Value)
    This now works ok, but it is incredibly slow. Doing these date conversions for one single row (i.e. two date fields) takes about 4 seconds.

    Do you know if there's any quicker way to achieve the same thing? At the moment I'm looping through row by row, doing the conversion then the comparison between values. Is there anything to be gained by doing the conversion for all rows first? Or is there some way where I can avoid a loop? Looping through 100 rows takes literally minutes at the moment (

Posting Permissions

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