Custom Time Formats?

[tber83]

Hey,

I've been trying to create a work schedule and I am having trouble formatting it just the way that I want it. Basically, I am looking to put in the time with an AM or PM after it.
The problem that I am having is that if the time is on the hour I want it to display as 4 PM and if it is not on the hour, then I want it to display as 4:30 PM. I don't want 4:00 PM with the 2 extra zeros. Is there any way to do this with conditional formatting or some type of formula? I just don't want to have to change the number format every time I have to enter a time that is not on the hour. Any ideas would be very helpful. Thanks so much for the great forum!

 

[Scott Huish]

Hi, welcome to the board!

This can be done with VBA. The following is working with range A1:A7, change as necessary.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Range("A1:A7"))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then
            If (c * 24) = Int(c * 24) Then
                c.NumberFormat = "h AM/PM"
            Else
                c.NumberFormat = "h:mm AM/PM"
            End If
        End If
    Next
Application.EnableEvents = True
End Sub

To put this code in:
Copy the code above.
Right click on the sheet tab for the sheet you want this to happen on.
Click on View Code.
Paste into white area.
Hit Alt-q
When you save the workbook this code will be saved with it.

 

[tber83]

Thanks for the Reply!

That looks great but it didn't seem to work. I don't have much experience using the VBA in Excel. I did everything just as you said and had no problems with that, but when I type the time into the cell it still comes up with the regular formatting. Is there something that might be missing or do I have to undo the formatting that I already had on the cell? Thanks again.

 

[Scott Huish]

What cell did you put the value in?

 

[tber83]

I put the value in cell A1.

 

[tber83]

Does it matter that I am running Excel 2002? I'm not sure if that would affect anything or not.

 

[Scott Huish]

I think it should still work, unless perhaps you had the cells formatted as text.

 

[tber83]

I finally got it to work. I think the marcos were disabled somehow due to the security, but they are working now. Thanks so much. If I have more than one range of cells that need this same formatting, can I just add the other ranges next to the one that already exists ... such as A1:A7;B9:B12;F3:F9;etc. ? Thanks.

 

[tber83]

I just have one other question besides the multiple ranges. I also have a different background color on every other row. I would like to be able to copy the text of one cell into another without copying the background colors. Is there a way to do this without doing Paste Special? Maybe a shortcut that I don't know about? Thanks again for all of the help!

 

[Scott Huish]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Target, Union(Range("A1:A7"), Range("B9:B12"), Range("F3:F9")))
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If IsNumeric(c) And c <> "" Then
            If (c * 24) = Int(c * 24) Then
                c.NumberFormat = "h AM/PM"
            Else
                c.NumberFormat = "h:mm AM/PM"
            End If
        End If
    Next
Application.EnableEvents = True
End Sub

In answer to your second question, since it's just one cell, don't copy it just assign the value to another cell.

For example, this will put the value of cell E1 in cell A1:

Range("A1")=Range("E1")