Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Converting Time into Minutes in a Userform Textbox

0

G'day, Fellow Excel users.

I'm in need of assistance from greater experts on 'Userform, Textbox' work around. I have this 'Excel Workbook', which I'm quite familiar with and use quite often on some other projects. This time I decided to make a 'Logbook for my Diving'.

My issue which I cannot work around is getting the formulas in vba, as I have never attempted in the past, I would have thought they would be the some as a normal worksheet, managed to get one or two going but not the below.

I have a 'Column.H and Column.I' which calculate the dive time in minutes.

Time.A  and Time.B

The formula is the product of Time.B – Time.A = to the dive time.

Column.H works fine

But, I need to convert this into minutes to work the 'Total Gas Used per Minute'

I tried several formulas but it got me beat I'm afraid.

I'll appreciate any help and assistance if some expert can throw some light on this issue.

I will upload the excel file as well.

I've used:

'Column H = Time Calculation, Time.B - Time.A

    Me.textbox6 = Format(TimeValue(textbox5.Text) - TimeValue(textbox4.Text), "hh:mm")

    Addme.Offset(0, 5).Value = Me.textbox6

'Column I = Time Calculation in Minutes

    Me.textbox7 = Me.textbox6

    'Me.textbox7 = Text(Me.textbox6 * 1440)

    Me.textbox7.Text = Format(Me.textbox7.Text, "hhmm")

    Addme.Offset(0, 6).Value = Me.textbox7

excel
posted by: jdgrapes (rep: 14) on Mon Nov 25, 2019 at 2:15 am
Answer
Discuss

Discussion

I think you should upload the sample file because I'm not understanding exactly where the issue occurs in your setup.
don (rep: 1989) Nov 26, '19 at 4:33 am
Many Thanks, the explaination was quite handy, although I had a crack at this and had different outcomes in my answers, I've upload the sample file I'm using. I also had to hadd some more code as if the time underwater is less than an hour there's no need to do a 'Hour to Minute' convertion, it worked for me here so I assume it is OK.
Many Thanks and Best Regards.
jdgrapes (rep: 14) Nov 26, '19 at 8:46 pm
So Very Close and so near; but still have one very last hurdle to jump I had to make a few, 'If' statements they both seem to be working. Although, with one IF statement when time exceeds “01:00” the statement seems to be working but it is failing to show on the Data worksheet; i. e the textbox4 stays blank. I’ve also clean a few textboxes as they’re not needed for and uploading a new logbk.   THE CODE IS AS BELOW   'Textbox Format and If Statements     Me.textbox1 = Format(Me.textbox1.Value, "00:00")     Me.textbox2 = Format(Me.textbox2.Value, "00:00")     Me.textbox3 = TimeValue(Me.textbox2.Value) - TimeValue(Me.textbox1.Value)     Me.textbox3 = Format(Me.textbox3.Value, "hhmm")     If Me.textbox3 <= "00:59" Then     Me.textbox4 = Me.textbox3.Value     If Me.textbox3 >= "01:00" Then     Me.textbox4 = Format(Me.textbox3.Value * 1440)     Me.textbox4 = Format(Me.textbox4.Value, ":00")     End If     End If Many Thanks for the Help
jdgrapes (rep: 14) Nov 30, '19 at 6:55 pm
I don't provide answers to follow-up questions. Consider dressing your requirement as a new question.
Variatus (rep: 4889) Nov 30, '19 at 11:51 pm
Add to Discussion

Answers

0
Selected Answer

You may be able to solve the problem yourself if you consider the following.

  1. A TextBox contains text. Therefore (because Text isn't the same as numbers) it doesn't contain numbers. VBA will be able to do something like Me.TextBox1.Value * 1440 but it takes an extra effort to first guess that the text in the TextBox can be converted to a number and then do the actual conversion. Much depends, therefore, on how you enter the minutes in the TextBox.
    1. So long as you enter only numbers and no characters the conversion should work fine for most uses (but not all). Just bear in mind what you are, in fact, asking VBA to do.
  2. Time is expressed as a decimal fraction of 1. The value of 1 represents one day.
    1. Therefore 0.5 stands for half a day or 12 hours.
    2. Format(0.5 "hhmm") will actually return 1200, i.e. 12 hours.
    3. 12 hours, of course, equals 720 minutes but Format(720, "hhmm") will return 0000 because the numeric value is greater than 1. In fact 720 stands for 720 complete days without any hours or minutes. Therefore the result is logically correct considering the hhmm mask..
    4. Therefore, if you intend to either enter minutes or show them you must convert between calculated time values and displayed results. The magic number is 1440, as you already appear to know. 1 minute = 1/1440 or 6.94444444444444E-04 . If you require less precision Round(1/1440, 5) = 0.00069 may do the job for you.
    5. If your TextBox requires the user to enter minutes, say numbers like 10 for 10 minutes, the conversion to a date/time value is Me.TextBox1.Value /1440. If VBA balks at this try Val(Me.TextBox1.Value). The Val() function converts the text in the textbox to a numberic value of Double type.
  3. The Format() function outputs a text string. So, Me.TextBox1.Value = Format(0.5 "hhmm") assigns the text string "1200" to the textbox's value, not the underlying numeric date/time value of 0.5. Excel has the capability to display 1200 while maintaining the cell value of 0.5. This is because in Excel the display format is contained in the cell's NumberFormat property. The TextBox only has a single format which can't be changed. It displays text. WYSIWYG is what they used to say when computers and I were both young. (What You See Is What You Get)
posted by: Variatus (rep: 4889) on Tue Nov 26, 2019 at 9:49 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login