Msgbox Time (Duration) Formatting When Over 24 Hours

Hi all, this is my first post so I hope I it is received well…

I am working on creating a time management system for the high school robotics team I am a coach for. I have (almost) everything else figured out except what should be a simple msgbox where I simply want to take the cumulative time a student has participated in either the ‘build season” or the “off season”. The spreadsheet that captures the time seems to be working as intended but I cannot figure out how to get the hours and minutes to appear in a msgbox correctly whenever the total is more than 24 hours. For example, if the student has participated for 25 hours and 57 minutes, this is displayed as 25:57 on the worksheet (cell format is [h];mm but I have tried numerous others including [hh]:mm, [h]:mm;@), however, in the msgbox it displays as 1 hour and 57 minutes. I have tried countless suggestions from this site and others but none of them have worked so far. Any ideas?

The only other issue I have is if the time logged in is after the time logged out (e.g. logged in at 17:00 (5pm) on July 12^th and logged out at 2:00 (2am) on July 13^th. While this will not happen too often, it will inevitably occur so if you have any suggestions as to how to handle this part, I would be most appreciative.

I have attached a screenshot of what my spreadsheet (“Build Data”) looks like. The 2 msgboxes show the issue I am having.

The date and time inputs are captured at the time that the participant enters their ID # in a UserForm ComboBox and clicks on an “OK” CommandButton which then contains the following (only included the portion relevant to the time):

Dim TimeLogged As Date
Dim LastInTime As Date


Sheets("Build Data").Range("I" & PasteRow) = Date [INDENT][COLOR=#008000]‘Captures the current system date as the Date Out[/COLOR][/INDENT]
 
Sheets("Build Data").Range("J" & PasteRow) = Time() [INDENT][COLOR=#008000]‘Captures the current system time as the Time Out[/COLOR][/INDENT]
 
LastInTime = Sheets("Build Data").Range("G" & LastInRow.Row) [INDENT][COLOR=#008000]‘Finds the last time this participant logged in and get the time from Time In[/COLOR][/INDENT]
  
TimeLogged = Time() – LastInTime [INDENT][COLOR=#008000]‘Calculates the Time Logged or difference between the Time Out and Time In as a duration[/COLOR][/INDENT]
  
Sheets("Build Data").Range("M" & PasteRow) = TimeLogged [INDENT][COLOR=#008000]‘Enters the duration as the Time Logged[/COLOR][/INDENT]
  
MsgBox Hour(Sheets("Build Data").Range("O" & PasteRow).Value) & " hours and " & Minute(Sheets("Build Data").Range("O" & PasteRow).Value) & " minutes" [INDENT][COLOR=#008000]‘Message to the participant to show how many off season hours they have logged[/COLOR][/INDENT]
  
MsgBox Hour(Sheets("Build Data").Range("N" & PasteRow).Value) & " hours and " & Minute(Sheets("Build Data").Range("N" & PasteRow).Value) & " minutes" [INDENT][COLOR=#008000]‘Message to the participant to show how many build season hours they have logged[/COLOR][/INDENT]

Thank you in advance for you help.

Is this question posted to the correct forum? Is there something else that I can provide to help someone providing some assistance to me?

Taking NoSparks advice, I am attaching a copy of the file. I already know that there are more efficient ways do some of what this does and that I have done some things inconsistently between some of the different procedures. I am still working through cleaning it up and will take any advice people may have, however, it is more important that I get the MsgBox Time (Duration) formatting issue resolved than it is to make the code more efficient/consistent.

File Layout
WorkSheet Visible - This is one of the ways that the user can interact with the file. The other is via a UserForm frmTracker that will open automatically when the file is opened (I have disabled frmTracker in this file but it looks and acts just like WorkSheet Visible).

WorkSheet Build Data - This is where the date and time events are captured. This is a cleaner version of the sheet that I posted a screenshot of originally where it correctly displays the cumulative time (duration) in columns N and O regardless of if it is more than or less than 24 hours.

WorkSheet Outreach Data - This can be ignored as it is not relevant to the issue.

WorkSheet Hours - In the final version of the MsgBox I want, the total required hours will come from cells B2 and E2. These are now just formatted as numbers so they work in the MsgBox as is, however, if formatting changes are being made, this could change.

WorkSheet Team Info - This can be ignored as it is not relevant to the issue.

WorkSheet Team Members - This can be ignored as it is not relevant to the issue although the code that is relevant will interact with this WorkSheet.

Step-By-Step Procedures To See The Issue

• Select WorkSheet Visible
• Click on the Red Check Out CommandButton within the Student Functions section.
• This will run Private Sub CommandCheckOut_Click in Sheet1.
• This will call to UserForm frmCheckOut
• frmCheckOut will open and prompt the user to enter their ID in the ComboBox cmbCheckOutID which will have a list of valid ID available in a drop down list.
• Select or type one of the numbers in and click on the OK CommanButton (CommandCheckOutOK)
• This will start running through Private Sub cmdCheckOutOK_Click in frmCheckOut
• After a number of validations, this sub will populate data into columns I, J, K, M, N & O on WorkSheet Build Data
• It will then cause 2 different MsgBoxes to pop up. The first is showing cumulative hours for that user in Column O on Worksheet Build Data and the second showing Column N on WorkSheet Build Data.

To avoid having to do extra steps, please just enter or select User ID "1" without the quotes when prompted in the UserForm. This will populate Columns I, J, K, M, N & O on Row 5 on WorkSheet Build Data.

• The first MsgBox will show cumulative hours in Cell O5 on WorkSheet Build Data which will be whatever number of hours and minutes are calculated as a result of running this procedure. Assuming you run it before 7am (Central US) on July 15, 2018, it will be under 24 hours and will show correctly.
• The second MsgBox should show the cumulative hours in Cell N5 on WorkSheet Build Data, specifically 25:57 to reflect the cumulative 25 hours and 57 minutes. However, it will actually only show 1 hour and 57 minutes in the MsgBox.

If you already ran this procedure, you can delete the data in cells I5, J5, K5, M5, N5 and O5 on Build Data and run the procedure again from the Visible tab.

I hope this is all clear and makes it easier for someone to look at.

Thank you in advance!

[USER="237394"]NoSparks[/USER], I have tried 15 or so different scenarios with different dates and times and it hasn't failed yet. I am still working through the logic as to why it works and will keep playing with it more to make sure it always returns the correct value but I thank you for your help!