VBA calculate sleep time, when time is entered in military time format

Hello all,

I searched the threads, but I couldn't find a solution to my challenge.

I need to calculate the hours of sleep (hours entered in military time) when the time spans over the 24 mark. For example I go to bed at 2200 and wake up at 0630, as a human I can calculate that I was laying in bed 8.5hrs, but programmatically I haven't been able to latch on to the correct formula to do so. Using TimeSlept = DateDiff("h", WakeTime, StartTime) does not even come close I get a resultant of 16. Converting the values to standard time (e.g. 10:00 pm and 06:30 am) still does not produce the correct output.

Tried many other posted solutions, but to no avail.

Any successful solutions are greatly appreciated.

Adriano

By the way, where are you getting the times from? Are you getting true times, or are you getting four-digit integers?

Hi Jeff,

Thanks for the reply.

Obviously I'm missing something, for I used your example to the letter and gotten -35656 as the resulting hours.

The times are entered into the user form as text, I tried both using text variables in the formula as well as integer, but the results are the same.

Can you help me with what variable definition did you use in your example?


Thanks again,

Adriano

Hello 6stringjazzer

The times are retrieved from a UserForm inputted into a text field and copied into a VBA string field.

I've tried many combinations (incantations): Using them as string, converting to integer via CInt, and just data casting into a integer field and running them through the suggested methods (with DateDiff and without), but the results are always wrong.

My environment is MS Office 2010.

Thanks for your help with this.....it's kicking my butt,
Adriano

The problem is that you are asking for four digits typed as text, and then converting them to an integer. The integer 0630 has nothing to do with the time 0630. How did you do this:

Converting the values to standard time (e.g. 10:00 pm and 06:30 am) still does not produce the correct output.

You need to convert this integer to a time if this is going to work. It would be very helpful to have your file. Lacking that, it would help if you showed all your code (remember to use code tags).

I would do something like this (didn't have time to test)

Please Login or Register  to view this content.

After that use the code I provided.

We are not communicating. When I wrote TextBoxWake, I meant you should use whatever the name of your text box is there. You didn't provide enough information for me to know what you were doing.

Can you just attach your file? Under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Also:

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer