Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Format a 'time' cell

    Hi loungers....I have a s/sheet where I am entering 'times' very frequently...eg: 9:30, 13:00, 14:00 etc etc.....I can't seem to find or figure out how to format the 'time' cells so that I can just enter 930 or 1330 or 1440 (note: without entering a colon) and have it format as 9:30 or 13:00 or 13:30 or 14:00.....any ideas as to what format I could use or what custom format I could use? Thanks.

  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
    You can't do it with a format, since the numbers are not times and must be converted. Check out the macros on Chip Pearsons site at http://www.cpearson.com/Excel/DateTimeEntry.htm which automatically convert the numbers

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Ok,,,thank you Steve

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....I went to Chip Pearson's site and found some code that enables me to enter a time (eg: 9:30am) as 930 in column K, and it will auto insert the colon separator....however, if the worksheet is protected, if I enter something in column B, it triggers the EndMacro message box and the macro won't run....if I unprotect the sheet, it works fine.....can you tell me what to add to the Chip Pearson code that will fix this? I imagine it is some sort of ActiveSheet.Unprotect line (and a Protect line at the end, but I don't know where to insert these lines into the code...I have attached the code as a text file...thank you.
    Attached Files Attached Files

  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
    I could not get this problem with the information you provided. BUT:Your code does not list what the macro "postpone" does. I presume the problem is with THAT procedure and further presume that at some point that the procedure "Postpone" tries to change a locked cell. If that is the case, in that code unprotect before it tries to change an unprotected cell and then reprotect after it.If that is not the issue, please provide a workbook demonstrating the problem and explain what I would do to generate the problem in the workbook.Steve

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....yes, there is code for the POSTPONE procedure, actually I think you helped me with it...that code is:

    Option Explicit
    Public Sub Postpone(Target As Range, strStatusCol As String, strDataStartCol As String, strDataEndCol As String)
    Const MsgTitle = "Trying to re-schedule ?...sorry..."
    Dim NewDateRow As Long
    Dim i As Long
    Dim iTargetCol As Integer
    Dim lngRow As Long
    Dim wks As Excel.Worksheet
    Dim rngCell As Excel.Range

    Set wks = Target.Worksheet
    lngRow = Target.Row
    iTargetCol = Target.Column

    Application.EnableEvents = False

    'Locate the first row of the requested new date
    With wks
    On Error GoTo BadDate
    NewDateRow = Application.WorksheetFunction.Match(Val(Target.Val ue), .Range("d:d"), 0)
    On Error GoTo 0

    If .Cells(NewDateRow, strStatusCol) = "Closed" Then GoTo BadDate

    'Populate the "Rescheduled from" cell
    ' ## APPEARS TO BE POPULATING COL D, BUT THAT'S THE ONE WE CHANGED ANYWAY?! ##
    ' Target.Value = .Cells(Target.Row, iTargetCol - iOffset + 1)

    'Locate the first available row
    For i = 0 To 10
    If .Cells(NewDateRow + i, strDataStartCol) = "" Then
    Exit For
    End If
    Next i
    If i = 11 Then
    GoTo BadDate
    Else
    NewDateRow = NewDateRow + i
    End If

    'Move data to new row for columns 1-6 before target
    For Each rngCell In .Range(.Cells(NewDateRow, strDataStartCol), .Cells(NewDateRow, strDataEndCol)).Cells
    rngCell.Value = .Cells(lngRow, rngCell.Column).Value
    If Right(UCase(Target.Value), 1) = "M" Then .Cells(lngRow, rngCell.Column).ClearContents
    Next rngCell

    Target.ClearContents

    .Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingRows:=True, _
    AllowFormattingColumns:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
    End With
    GoTo ExitHandler

    BadDate:
    On Error GoTo 0
    MsgBox "" & "The selected date is not a Court day.", _
    vbCritical, MsgTitle
    Target = ""

    ExitHandler:
    Application.EnableEvents = True
    End Sub
    Public Sub MoveData(lngOld As Long, lngNew As Long, strCol As String)
    Range(strCol & lngNew) = Range(strCol & lngOld)
    Range(strCol & lngOld).ClearContents
    End Sub

    ......to move (or copy) the data in columns F:AE from one row to another (eg: from row 25 to row 47), you go to B25 and enter 47 (to copy) or 47M (to move) the data to row 47......it the sheet is protected, it triggers the ENDMACRO MsgBox ("You did not enter a valid time") and doesn't perform the copy (move).........if the sheet is unprotected, it works fine....my diffciulty is knowing where to insert the UNPROTECT and PROTECT code.....does this explain it better?

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Chatham, UK
    Posts
    15
    Thanks
    0
    Thanked 6 Times in 4 Posts
    If you don't mind numbers looking like a time value, you can use this custom format 00":"00

  8. #8
    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
    Add the line: "ActiveSheet.Unprotect" before the "For Each rngCell In"and after the line "Next rngCell" add the line "ActiveSheet.Protect"Steve

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you for that Steve...I will set it up like that and give it a test.

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you Simon.....as with many things, the simpler the better....what I am doing is trying to find a format that will allow users to enter numbers (eg: 930, 1000, 1400 etc) and have them automatically formatted as times -- your suggestion is perfect bc users don't need to hit shift-colon to enter the time separator - only the numbers, so this is ideal....thank you !

  11. #11
    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
    Note that Simon's method will display it as 9:30 bu the number in the cell will remain 930, so can not be used for calculations of time differences unless the calculation adjusts this.Steve

Posting Permissions

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