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 12th and logged out at 2:00 (2am) on July 13th. 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]
Display More
Thank you in advance for you help.