Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format Dates Only (2002)

    I have data which is imported from a main frame every week and I need to make all dates the same format eg mm/dd/yyyy. There is always 20 columns but the number of rows can vary. The data is also a mixture of text, dates, currency etc. The dates are often in the same columns but this is not guaranteed. How can I make Excel select date cells only and apply specific formatting to them?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Dates Only (2002)

    You could use this macro:

    Sub ApplyDateFormat()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If IsDate(oCell) Then
    oCell.NumberFormat = "mm/dd/yyyy"
    End If
    Next oCell
    End Sub

    Note: this macro will leave cells that contain only a time alone (example: 10:52) but it will format cells that contain a date and time (example: December 22, 2003 10:52 AM becomes 12/22/2003).

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Dates Only (2002)

    That is excellent. It works perfectly, and quickly too! Thanks.
    Can a similar thing be done for currency too to standardise that? How would I go about that?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format Dates Only (2002)

    There is no IsCurrency function, and in general, the problem is how to distinguish currency from other numeric data. Perhaps somebody wrote 23.45 and meant $ 23.45. If all currency cells contain a $, and no other cells contain a $, you can use this variation on my previous reply:

    Sub ApplyCurrencyFormat()
    Dim oCell As Range
    For Each oCell In ActiveSheet.UsedRange
    If InStr(oCell.NumberFormat, "$") > 0 Then
    oCell.NumberFormat = "$ #,##0.00_-"
    End If
    Next oCell
    End Sub

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format Dates Only (2002)

    Again, thank you so much. You have saved me hours of work!

Posting Permissions

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