Results 1 to 15 of 15
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    VBA Code to convert Numbers to Time

    I have the following time sheet and have tried to write code that where the user enter a single number for eg 9 , than 9:00am must appear, if the user enter a double digit number for eg 10 , 14 , then 10:00am, 2:00pm must appear in the range D8:G14


    See attached spreadsheet


    It would be appreciated if someone could kindly amend my code so as to generate the desired result
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Howard,

    Place the following code in the worksheet module then format the range on the sheet to Time 1:30 PM. If the user enters a valid number, it will be converted to the format h:m AM/PM

    Time1.png

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target = "" Then Exit Sub
    If Not Intersect(Target, Range("D8:G14")) Is Nothing Then
    Application.EnableEvents = False
        Target = TimeValue(Str(Target) & ":00")
    Application.EnableEvents = True
    End If
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Howard,

    Why not do this with Data Validation instead of VBA? I've done Start & Start Lunch to get you started.
    Time sheets-RG1.xlsm
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    HowardC (2015-09-05)

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Howard,

    Here is the code adapted to your sheet.
    Attached Files Attached Files

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2015-09-05)

  7. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Maud & RG for your input, much appreciated

  8. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi RG

    Thanks for the help once again

    If I enter times from midnight to 05:59, I, 01 cannot get it to work, for eg 24:00, 24:30, 01:00, 02:00 etc

    It would be appreciated if you could assist me
    Attached Files Attached Files

  9. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    I have re-tested the data. Whatever figure I enter, I get 12:00 am as the result

    It would be appreciated if you would kindly test and amend your code
    Attached Files Attached Files

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Howard,

    The code was stopped after the Application.EnableEvents= False was executed but before the Application.EnableEvents= True was executed to re-enable it. Entering text in a time slot would have caused this. I added some error trapping to my code that will prevent this. Good beta testing!

    FYI... you can add the following code to the ThisWorkbook module so you do not have to protect, run your code, then re-protect the sheet. The UserInterFaceOnly:=True parameter allows the code to function on a locked sheet

    Code:
    Private Sub Workbook_Open()
    Worksheets("TimeSheet").Protect _
                DrawingObjects:=True, _
                Contents:=True, _
                UserInterFaceOnly:=True
    End Sub
    Attached Files Attached Files

  11. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for the modification. When entering a fraction of an hour for eg 930 , 915, 1015, I get an error message -"you have entered a value that cannot be converted into time. These should appear as 09:30 am, 09:15 am, 10:15am etc

    Kindly amend your code to accommodate fractions of an hour

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by HowardC View Post
    If I enter times from midnight to 05:59, I, 01 cannot get it to work, for eg 24:00, 24:30, 01:00, 02:00 etc
    Howard,

    It was designed not to allow times between 12am and 6am in the Data Validation Rule.
    HowardNoMorning.JPG
    Adjust as follows:
    HowardANyTime.JPG

    BTW: Using this method of data validation you can enter times w/AM or PM or use Military time.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks RG

  14. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    ..where the user enter a single number for eg 9 , than 9:00am must appear, if the user enter a double digit number for eg 10 , 14 , then 10:00am, 2:00pm must appear in the range D8:G14
    Howard,

    You did not mention segments of the hour. Here is the adjusted code.

    Note: The range D8:G14 must be formatted as text

    HTH,
    Maud
    Attached Files Attached Files

  15. #13
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud


    Thanks for all your help

    My apologies for not mentioning to you initially that I needed segments of the hour. I did not think of this when setting up the spreadsheet. Only when I was testing did I realize I needed segments of the hour.



    I need a small change if possible

    I do not want to display seconds for eg 9:00:00 AM -I would like to display this as 9:00 AM

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    NP Howard,

    See this revision.

    Maud
    Attached Files Attached Files

  17. The Following User Says Thank You to Maudibe For This Useful Post:

    HowardC (2015-09-06)

  18. #15
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks Maud

    Code works perfectly

Posting Permissions

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