Userform Time Drop down Calculation

Nisio

New member
Joined
Jun 26, 2017
Messages
1
Reaction score
0
Points
0
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...

[FONT=&quot]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).

[/FONT]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


[FONT=&quot]
Many thanks for any help you can offer!

Regards

Nisio
[/FONT]
 

Attachments

  • times.xls
    35 KB · Views: 38
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
 
cross posted without links:
https://www.mrexcel.com/forum/excel...-24hr-clock-associated-calcs.html#post4854767
https://www.excelforum.com/excel-pr...90504-time-calculation-on-userform-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…
 
Back
Top