Calculate time difference between Excel Time Combo Boxes

I want to put some combo boxes on an Excel form, populate them with time values in 15 minute increments and then calculate the difference between them in cells. Any help will be appreciated.
Buck_BeasomDatabase DesignerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FlysterCommented:
Here's one way it can be done. Two activeX combo boxes and one text box added to form. Column A & B were used to populate the combo boxes. VBA code was added to the combo boxes change events. To see code go to the developer tab and select visual basic ( or just go alt+f11)

Flyster
ComboBox.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

Perhaps you meant something similar to the attached workbook.

If you open it, you will see a UserForm where you can select a "From" & a "To" time.  The [Calculate] button will calculate the difference & add a row to the worksheet [Q_28251101].

Close the UserForm with the [x] Close button when you have finished updating as required.

Here is the code taken from the "frmQ_28251101" (form) code module:

Option Explicit
Private Sub cmdCalculate_Click()

  Dim lngRow                                            As Long
  
  On Error Resume Next
  
  lngRow = Worksheets("Q_28251101").Cells(Cells.Rows.Count, 1).End(xlUp).Row + 1&
  
  Worksheets("Q_28251101").Cells(lngRow, 1) = Me.cboFrom.Value
  Worksheets("Q_28251101").Cells(lngRow, 2) = Me.cboTo.Value
  Worksheets("Q_28251101").Cells(lngRow, 3) = Format(TimeValue(Me.cboTo.Value) - TimeValue(Me.cboFrom.Value), "hh:mm")
  
End Sub
Private Sub UserForm_Initialize()

  Dim datLoop                                           As Date
  
  On Error Resume Next
  
  Me.cboFrom.Clear
  Me.cboTo.Clear
  
  For datLoop = TimeValue("00:00") To TimeValue("23:45") Step TimeValue("00:15")
      Me.cboFrom.AddItem Format$(datLoop, "hh:mm")
      Me.cboTo.AddItem Format$(datLoop, "hh:mm")
  Next datLoop
  
  Me.cboFrom.ListIndex = 0&
  Me.cboTo.ListIndex = 0&
  
End Sub

Open in new window


This code is present within the (This)Workbook code module, "wbkQ_28251101":

Option Explicit
Private Sub Workbook_Open()

  On Error Resume Next
  
  frmQ_28251101.Show
  
End Sub

Open in new window



Thank you for reviewing the workbook & advising if this meets your requirements.

BFN,

fp.
Q-28251101.xls
0
Buck_BeasomDatabase DesignerAuthor Commented:
I'm afraid I'm clunking this up by not explaining my goal. Here's what I am after:

Cell B2 - user picks from a list of time values in 15 minute increments. I have figured out how to make the cell a drop down using "Data Validation" and "List", but I have to type all of the values into the list separated by commas. I'd rather refer to a named range, but I'm having problems getting that to work.

Cell B3 - Same as B2.

Cell B4. Subtract cell B2 from cell B3, but I want it to be in hours or fractions. When I select 5:30 AM in the first cell and 6:30 AM in the second cell and perform the calculation, I get ".04" as a value. So there is something else I need to do to convert that into hours and quarter of an hour fractions.

This should be simple, but I've gotten spoiled using Access and am not too familiar with working with time - or "list boxes" in Excel.

Thanks
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

[ fanpages ]IT Services ConsultantCommented:
Hi again,

I am a little confused why you would have quarter of an hour difference if the values to be selected are on the hour, or on the half hour.

However, please look at the attached workbook, & adjust the named range [rngTimes] I have created in column [T] if you wish to include further sub-divisions of each hour.

Cell [B2] ("From"), as you mentioned, uses Data Validation (based on the [rngTimes] range).
Cell [B3] ("To"), is the same.

Cell [B4] ("Result") contains this formula:
=IF(OR(ISBLANK(B2),ISBLANK(B3)),"",IF(B3>=B2, B3-B2, "invalid"))

It checks for an entry in both cell [B2] & [B3], & also that the [B3] is greater than, or equal to, [B2].

Cell [B2] has the NumberFormat: h:mm AM/PM
Cell [B3]: (the same)
Cell [B4]: h:mm (without the AM/PM suffix)

BFN,

fp.
Q-28251101b.xls
0
Buck_BeasomDatabase DesignerAuthor Commented:
BFN.

This is PERFECT! I am using quarter hour increments because this is a time sheet and that is how they calculate pay.

Only remaining question. If I want to put the Time range on a different tab and hide that tab (to keep the unskilled from stepping on it) how does the range reference change?

Will post the points when I hear back - either way. You got me what I needed!

Thanks.
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

The only difference is the reference in the Data Validation cells, & any already defined will be changed automatically when the range is moved.

For example, in the attached workbook, I added a new worksheet, [Times].

I then selected column [T] in the worksheet [Q_28251101], cut this selection to my Windows Clipboard, & pasted within column [A] of the new worksheet, [Times].

I then hid the new worksheet.

The Data Validation continued to work as expected.

Please review the attachment, or replicate the steps yourself.

Thanks.

B)ye F)or N)ow,

fp.
Q-28251101c.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.