Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CONDITIONAL DATE/TIME FORMATTING (2002)

    Is there any way to code so that certain cells will display with a format of "hh:mm AM/PM" if the date is today, but with a format of "mm/dd/yyyy hh:mm AM/PM" if the date is not today ?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    AFAIK, Not directly.
    Conditional formatting does not change the number formatting only the display options
    Custom formatting does not allow the use of functions so you can not change the display.

    Some options: keep the real date/times in a cell and in another cell use a formula like:
    =TEXT(A1,IF(INT(A1)<>TODAY(),"mm/dd/yyyy ","")&"hh:mm AM/PM")

    to display that date (this cell will result in text, but A1 is a date/time.

    Other options would be a macro. You could (eg on workbook_open) have code to review the desired range and change the formatting as desired for the cells.

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    Type : =IF(A1=TODAY(),TEXT(A1,"hh:mm AM/PM"),TEXT(A1,"mm/dd/yyyy hh:mm AM/PM")) into the column next to the column that contains the date!
    Regards,
    Rudi

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

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    Apart from the options already mentioned, you could use the Worksheet_Change event if the dates/times are entered manually. It won't work if they are the result of formulas, in that case you'll have to use one of the other options.

    Right click the sheet tab.
    Select View Code.
    Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A1:A10")).Cells
    If IsDate(oCell.Value) Then
    If Int(oCell.Value) = Date Then
    oCell.NumberFormat = "hh:mm AM/PM"
    Else
    oCell.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    End If
    Else
    oCell.NumberFormat = "General"
    End If
    Next oCell
    End If
    End Sub

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    A problem with this, is that if they are entered today, when tomorrow comes, yesterday's dates are marked incorrectly (it will not update as time passes...)

    Steve

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

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    <img src=/S/sad.gif border=0 alt=sad width=15 height=15> <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

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

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    To solve that problem, you could put this modification to your code in the Worksheet Calculate event routine and put a =Today() function in a hidden cell somewhere on the sheet:

    <pre>Private Sub Worksheet_Calculate()
    Dim oCell As Range
    For Each oCell In Intersect(Target, Range("A1:A10")).Cells
    If IsDate(oCell.Value) Then
    If Int(oCell.Value) = Date Then
    oCell.NumberFormat = "hh:mm AM/PM"
    Else
    oCell.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    End If
    Else
    oCell.NumberFormat = "General"
    End If
    Next oCell
    End Sub
    </pre>

    Legare Coleman

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

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    Thanks, that should do it. (I tend to avoid the Worksheet_Calculate event for fear of slowing down things; perhaps that fear is not realistic any more with today's PCs)

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CONDITIONAL DATE/TIME FORMATTING (2002)

    Thank you Hans, Steve & Legare!

    On the matter of "slowing things down" this application will have almost no formulas (but quite a few macros), it is for a journaling / reporting form. The times are manually entered for event start/end entries along with some cells for event type, description, etc. Since some events can take longer than a day to resolve I wanted this conditional format.

    Also, an extra thank you to Hans for answering my related post elsewhere. As you correctly noted these two questions are related but separate problems.

Posting Permissions

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