Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Cleaning' a date entry (2003, 97)

    I am using conditional formatting that compares dates then applies formatting based on the results of the comparison. the problem is that when i import date data (from MS Project to Excel), it comes in as "4/3/06 8:00" and a comparison to "4/3/06" (no quotes in either case) comes back as FALSE and I do not get the formatting desired. Is there any way I can change "4/3/06 8:00" to "4/3/06"? I have tried reformatting and it displays what I want but still presents the same problem.

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

    Re: 'Cleaning' a date entry (2003, 97)

    In the conditional format formula, use the INT() function in the reference to the cell containing that date. For example, if that date is in A1, use INT(A1).
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Oct 2001
    Location
    San Bernardino, California, USA
    Posts
    734
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Cleaning' a date entry (2003, 97)

    Thank you - exactly what I needed. I never knew INT applied to dates.

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

    Re: 'Cleaning' a date entry (2003, 97)

    Excel keeps Date/Time values as numbers. The date is the number of days since December 31, 1899 (ie. 1 is Jan. 1, 1900), and the time is kept as the fractional part of a day (ie. .25 is 6:00 and .5 is noon). Using INT removes the time from the date/time.
    Legare Coleman

Posting Permissions

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