none
Trying to modify a timesheet RRS feed

  • Question

  • Trying to modify a timesheet that accepts ; and replaces it with :, while also accepting :. Also only accepting a user to input every quarter hour(accepting 24 hr entries and converting it to 12 hr.. ex. 8:00, 11:15, 15:30, 5:45.

    If Target.Count = 1 Then
         Else: MsgBox ("Only fill out One box at a time please.")
    
         'Convert ; to : first
         If Target.Value = ";" Then
             Target.Value = Replace("Target.Value", ";", ":")



        
    • Edited by danishaniModerator Monday, December 1, 2014 7:45 PM Modified question for better format and readability
    Monday, December 1, 2014 7:11 PM

Answers

  • This should allow entries of 8:45 (time) 8;45 (time with a semi-colon) or 845 (time with no delimiter), and restrict them to quarter-hours:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim dEntry As Date
        If Target.Count <> 1 Then
            MsgBox "Only fill out One box at a time please."
            Exit Sub
        Else
            Application.EnableEvents = False
            'Convert ; to : first
            If InStr(1, Target.Value, ";") <> 0 Then
                dEntry = TimeValue(Replace(Target.Value, ";", ":"))
            Else
                If Target.Value > 1 Then
                    Target.Value = (Target.Value \ 100) / 24 + (Target.Value Mod 100) / 1440
                End If
                dEntry = Target.Value
            End If

            Target.Value = Round(dEntry * 96, 0) / 96
            Target.NumberFormat = "hh:mm"
            Application.EnableEvents = True
        End If
    End Sub

    • Marked as answer by 40Espada6 Wednesday, December 3, 2014 2:58 PM
    Tuesday, December 2, 2014 9:24 PM

All replies

  • I have assumed that the cells are formatted as time

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim dEntry As Date
        If Target.Count <> 1 Then
            MsgBox "Only fill out One box at a time please."
            Exit Sub
        Else
            Application.EnableEvents = False
            'Convert ; to : first
            If InStr(1, Target.Value, ";") <> 0 Then
                dEntry = TimeValue(Replace(Target.Value, ";", ":"))
            Else
                dEntry = Target.Value
            End If

            Target.Value = Round(dEntry * 96, 0) / 96

            Application.EnableEvents = True
        End If
    End Sub

    You could also replace

               Target.Value = Round(dEntry * 96, 0) / 96

    with this, to force quarter hour entries only:
                If dEntry <> Round(dEntry * 96, 0) / 96 Then
                    MsgBox "Only quarter hour entries please."
                    Target.Value = ""
                End If

    Tuesday, December 2, 2014 5:00 PM
  • So far this is great Mr. Deitrick

    The InStr checks times already put in but I want to be able to type 8;45 or 845 and it output 8:45

    Tuesday, December 2, 2014 7:13 PM
  • What are your cells formatted as - Text? Time? General?
    Tuesday, December 2, 2014 7:19 PM
  • General
    Tuesday, December 2, 2014 8:13 PM
  • This should allow entries of 8:45 (time) 8;45 (time with a semi-colon) or 845 (time with no delimiter), and restrict them to quarter-hours:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim dEntry As Date
        If Target.Count <> 1 Then
            MsgBox "Only fill out One box at a time please."
            Exit Sub
        Else
            Application.EnableEvents = False
            'Convert ; to : first
            If InStr(1, Target.Value, ";") <> 0 Then
                dEntry = TimeValue(Replace(Target.Value, ";", ":"))
            Else
                If Target.Value > 1 Then
                    Target.Value = (Target.Value \ 100) / 24 + (Target.Value Mod 100) / 1440
                End If
                dEntry = Target.Value
            End If

            Target.Value = Round(dEntry * 96, 0) / 96
            Target.NumberFormat = "hh:mm"
            Application.EnableEvents = True
        End If
    End Sub

    • Marked as answer by 40Espada6 Wednesday, December 3, 2014 2:58 PM
    Tuesday, December 2, 2014 9:24 PM
  • It kinda works until I go to enter a military time. It forces it to 0:00
    Wednesday, December 3, 2014 1:42 PM
  • nevermind no it doesn't. Military time works. Although after I type in say 8:22, It will display 0:00 then when I click on that same cell it will change it to 8:30. How can you have it change automatically to 8:30 right after entering 8:22 without having to click back on it. This is a tough one and I thank you so much. This is helping me a lot.
    Wednesday, December 3, 2014 2:12 PM
  • Are you using the Change event or the SelectionChange event? You never posted the entire macro....
    Wednesday, December 3, 2014 5:34 PM
  • Doh! SelectionChange. Oh man I apologize I thought it was part of what I copy and pasted
    Wednesday, December 3, 2014 6:44 PM
  • I usually require a few self-applied sharp smacks to the forehead, too. ;-)
    Wednesday, December 3, 2014 7:16 PM
  • I've been trying to learn basic programming for about 3 months now and everyone I've talked to who has tried or is programming says mistakes are GOING to happen. It's inevitable haha
    Wednesday, December 3, 2014 8:21 PM