PDA

View Full Version : vba adding up time with spin control & text box with userfirendly output



Rasscal
02-06-2020, 04:40 AM
hi all,


i have a useform textbox controlled by a spin button that a user will input time taken to complete a task, this works fine in increments of 15mins (0.25)

however the output isnt very user friendly,

me.txthours.value is then sent to a DB and a calculation (value*hourly rate) hence why its in 0.25 values.


Private Sub sbTime_SpinUp()


Me.txtHours.value = Me.txtHours.value + 0.25


End Sub




value output displayed
Disired Display value


0.25
15


0.5
30


0.75
45


1
1


1.25
1:15


1.5
1:30


1.75
1:45


2
2


2.25
2:15


2.5
2:30


2.75
2:45


3
3




any help with this would be greatly appreciated.

JKwan
02-06-2020, 07:18 AM
MsgBox Format(.25 / 24, "hh:mm")

Rasscal
02-06-2020, 08:48 AM
MsgBox Format(.25 / 24, "hh:mm")


can you please elaborate im struggling to get this to work

p45cal
02-06-2020, 09:27 AM
A TextBox can only contain, erm…, oh that's right, text.

Private Sub sbTime_SpinUp()
Me.txtHours.Value = Format(TimeValue(txtHours.Value) + TimeValue("00:15:00"), "hh:mm")
End Sub

The value in hours to the DB will be TimeValue(txtHours.Value) * 24

Instead of + TimeValue("00:15:00") you could use + 1/96

JKwan
02-06-2020, 10:27 AM
ah, silly me.... did not think of that, p45cal