Custom Time Formats?

tber83

New Member
Joined
Sep 8, 2008
Messages
9
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!
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
Hi, welcome to the board!

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

Code:
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

New Member
Joined
Sep 8, 2008
Messages
9
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
What cell did you put the value in?
 

tber83

New Member
Joined
Sep 8, 2008
Messages
9

ADVERTISEMENT

I put the value in cell A1.
 

tber83

New Member
Joined
Sep 8, 2008
Messages
9
Does it matter that I am running Excel 2002? I'm not sure if that would affect anything or not.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows

ADVERTISEMENT

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

tber83

New Member
Joined
Sep 8, 2008
Messages
9
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

New Member
Joined
Sep 8, 2008
Messages
9
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
Try this:

Code:
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:

Code:
Range("A1")=Range("E1")
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,718
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top