Results 1 to 3 of 3

Thread: Userform Time Drop down Calculation

  1. #1

    Userform Time Drop down Calculation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello people,

    I would truly appreciate any help you can offer on the following problem.

    I am creating a pretty complex (for me!) userform/macro which records work times for a number of technicians.


    it works over a 24 hour clock and the calculation/recording issues are as follows:

    Midnight - 9AM - Overtime - Store category, times entered and calculate duration
    5.30PM- Midnight Overtime - Store category, times entered and calculate duration
    9.30AM - 5.30PM Regular time - Store category, times entered and calculate duration - HOWEVER...

    Hello people,

    I would truly appreciate any help you can offer on the following problem.

    I am creating a pretty complex (for me!) userform/macro which records work times for a number of technicians.


    it works over a 24 hour clock and the calculation/recording issues are as follows:

    Midnight - 9AM - Overtime - Store category, times entered and calculate duration
    5.30PM- Midnight Overtime - Store category, times entered and calculate duration
    9.30AM - 5.30PM Regular time - Store category, times entered and calculate duration - HOWEVER...

    With regular, disregard the time from 1-2pm - if time entered includes this or any part of it, remove the offending time
    EX: 9.30am-5.30pm - store Regular, 9am, 5.30pm, 7.5hours
    12.30pm - 2.15pm - store Regular, 12.30pm , 2.15pm 0.75hours
    11am - 3pm- store Regular, 11am, 3pm- 3 hours

    Of course, someone may also entered a time requiring up to three entries:
    3AM-11PM
    Store 1: Overtime, 3AM, 9AM, 6
    Store 2: Regular, 9am, 5.30pm, 7.5hours
    Store 3: Overtime, 5.30PM, 11PM, 5.5hours


    You see my problem? I have been so immersed in this that I now cannot see the "wood from the trees" and would truly appreciate any help.

    I have attached a slimmed down userform showing the input side of the time entry (with a little cheat to include the midnight hour figure for the finishing time).

    If you view the VB section of the file, and click on Forms, you will see the code ...

    Developer .. Macro.. TimeTest.. Forms... xxxxFTest... Run UserForm



    Many thanks for any help you can offer!

    Regards

    Nisio
    Attached Files Attached Files

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    794
    Articles
    0
    Excel Version
    Excel 2010
    Try this with your second combobox
    Code:
    Private Sub TimeDone_Change()
    
    If Me.TimeDone.Value = "" Then Exit Sub
    
    start_time = TimeValue(Me.ComboBox1)
    end_time = TimeValue(Me.TimeDone)
    
    Duration = Abs((end_time - start_time) - (start_time > end_time)) * 24
    
    start_break = TimeValue("1:00:00 PM")
    end_break = TimeValue("2:00:00 PM")
    
    'only dealing with 5 possibilities
    '1   completely before break
    If start_time < start_break And end_time <= start_break Then deduction = 0
    '2   completely after break
    If start_time >= end_break And end_time > end_break Then deduction = 0
    '3   start before break and end during break
    If start_time < start_break And end_time > start_break And end_time <= end_break Then
        deduction = (end_time - start_break) * 24
    End If
    '4   start before break and end after break
    If start_time < start_break And end_time > end_break Then
        deduction = (end_break - start_break) * 24
    End If
    '5   start during break and ending after break
    If start_time >= start_break And start_time < end_break Then
        deduction = (end_break - start_time) * 24
    End If
    '6  start and end within break (less than break hour)
    '7  start and end within break (more than 23 hours)
    
    MsgBox Format(Duration - deduction, "0.00") & "  hrs"
    
    End Sub

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,740
    Articles
    0
    Excel Version
    365
    cross posted without links:
    https://www.mrexcel.com/forum/excel-...ml#post4854767
    https://www.excelforum.com/excel-pro...orm-entry.html

    Nisio, for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

    The same rules apply at both mrExcel and ExcelForum by the way…

Tags for this Thread

Posting Permissions

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