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
Whoops .. As I cannot post and attachment, I list below the entire content of my slimmed down Userform ..
Public dTime As Date
Public dtimeOut As Date
Private Sub ComboBox1_Change()
TimeDone.Clear
For dTime = "0:00 AM" To "23:59PM" Step "00:15"
ComboBox1.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
dtimeOut = (TimeValue(ComboBox1.Value) + TimeValue("00:15"))
For dTime = dtimeOut To "23:59PM" Step "00:15"
Me.TimeDone.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.Clear
' For dTime = "0:00 AM" To "23:59PM" Step "00:15"
For dTime = "0:00 AM" To "23:59PM" Step "00:15"
Me.ComboBox1.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
End Sub
Many thanks for any help you can offer!
Regards
Nisio
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
Whoops .. As I cannot post and attachment, I list below the entire content of my slimmed down Userform ..
Public dTime As Date
Public dtimeOut As Date
Private Sub ComboBox1_Change()
TimeDone.Clear
For dTime = "0:00 AM" To "23:59PM" Step "00:15"
ComboBox1.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
dtimeOut = (TimeValue(ComboBox1.Value) + TimeValue("00:15"))
For dTime = dtimeOut To "23:59PM" Step "00:15"
Me.TimeDone.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
End Sub
Private Sub UserForm_Initialize()
Me.ComboBox1.Clear
' For dTime = "0:00 AM" To "23:59PM" Step "00:15"
For dTime = "0:00 AM" To "23:59PM" Step "00:15"
Me.ComboBox1.AddItem Format(dTime, "h:mm AM/PM")
Next dTime
End Sub
Many thanks for any help you can offer!
Regards
Nisio