subtracting time in a userform

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
531
Office Version
  1. 2019
Platform
  1. Windows
Hello, I have a userform that I use to enter data in a workbook and all that works well. Currently I time stamp the entry using the Now() function when the data is entered. On the equipment that is being inspected there is a timer that gives us the amount of elapsed time when it performed a particular action. I want to tweak the form by adding a text box where the user can add the value on the timer (it is "X" number of minutes) into the text box and then have the code subtract that value from the now() when the code is executed. This would give us a rough estimate of when that particular action occurred.

Originally the string for the time stamp was ".Range("D" & nextrow) = Now()" Not knowing exactly where to start I tried something simple like adding the "TimeLog.Value" to the string:


VBA Code:
Private Sub EnterData_Click()

 Dim nextrow As Long
 
'some code here....
 
'transfer the data
With ActiveSheet

  nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
 
     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = TimeLog.Value - Now()    
     .Range("G" & nextrow) = CommentBox.Value
     
End With
End Sub


wasn't surprised it didn't work, thinking that the code does not know the value in the text box is minutes, so I need to resolve that at least. Can anyone offer suggestions on how to go forward? I appreciate any input - thanks,
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
When formatted as a Double, a Date variable or the return value from the Now() function is an integer and a decimal. The integer part is the number of days since Dec. 31, 1898. So this code "?Dbl(Now())" run in the Immediate window today displays 44330.####. Depending on the time you run it, the decimal will be different values.

The decimal values indicate the time during the day. 0.5 is noon, 0.25 is 6 AM, etc.

In the code below, it figures out how much one minute is worth (dividing 1 by number of hours in a day (24) and that again by the number of minutes in an hour (60)). Since you indicated that the number in the TextBox would be minutes, that value is multiplied by the one minute value to give the decimal value that is subtracted from Now to give, for example, 5 minutes ago (or whatever number of minutes is in the TextBox).

VBA Code:
Private Sub EnterData_Click()

 Dim nextrow As Long
 Dim oneMin As Double
 oneMin = 1 / 24 / 60

'some code here....
 
'transfer the data
With ActiveSheet

  nextrow = .Range("A" & Rows.Count).End(xlUp).Row + 1
 
     .Range("A" & nextrow) = ChemicalBox.Value
     .Range("B" & nextrow) = LotNumber.Value
     .Range("C" & nextrow) = TechName.Value
     .Range("D" & nextrow) = Now() - (TimeLog.Value * oneMin)
     .Range("G" & nextrow) = CommentBox.Value
   
End With
End Sub
 
Solution

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
531
Office Version
  1. 2019
Platform
  1. Windows
Wow! that worked perfect shknbk2, thank you very much for that and thank you even more for the explanation of the mechanics of the syntax.
 

Forum statistics

Threads
1,143,909
Messages
5,721,465
Members
422,363
Latest member
Bogus_Potatoes

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top