+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    Chapel Hill, NC
    MS-Off Ver
    2010
    Posts
    7

    Question 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

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,284

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

    Military time vs. AM/PM time is a matter of how it's displayed. It has no effect on calculations.

    Try this:
    Please Login or Register  to view this content.
    Tested.
    Jeff
    | | |?| |?| |?| |?| | |:| | |?| |?|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,284

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

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

  4. #4
    Registered User
    Join Date
    04-09-2014
    Location
    Chapel Hill, NC
    MS-Off Ver
    2010
    Posts
    7

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

    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

  5. #5
    Registered User
    Join Date
    04-09-2014
    Location
    Chapel Hill, NC
    MS-Off Ver
    2010
    Posts
    7

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

    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

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,284

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

    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.

  7. #7
    Registered User
    Join Date
    04-09-2014
    Location
    Chapel Hill, NC
    MS-Off Ver
    2010
    Posts
    7

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

    Good morning 6StringJazzer and all.

    I've tried the suggested code, but I still cannot get it to work.
    Below is as near of a copy of my code as I can provide except the UserForm itself.

    Basically I have created a UserForm with three Text Boxes (TextBox1, TextBox2 and TextBox3) which receive
    1) The first two boxes accept user input in 24 hour military time (0001 to 2359)
    2) The third a decimal "period" separated hours slept (e.g 1.0 - 8.5)

    I copy those values into three string variables in my code (TextBoxStart = TextBox1.Value, TextBoxWake = TextBox2.Value and TextBoxSlept = TextBox3.Value)

    Have applied the suggested code and get the following into TimeSlept "1/8/1900 12:00 PM" from the operation (TimeSlept = ((1 - StartTime) + WakeTime) * 24)

    The purpose of my code is to compare programmatically the user provided "time slept" to the time spent (or not) in bed. The problem is the time spanning past midnight.

    Any help to understand how to accomplish this is greatly appreciated.


    Code snippet
    ****************************************************************************
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 09-29-2017 at 10:28 AM.

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    21,284

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 05-30-2017, 04:45 PM
  2. [SOLVED] time sheet help, format TIME and formula based on the actual time entered.
    By bh mng in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2016, 01:10 PM
  3. Calculate passage of time utilizing military time for hospital use
    By elebarbb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-03-2016, 07:17 PM
  4. Need to calculate elapsed time, but excel won't recognize the format as time.
    By sccrfraggle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-08-2015, 10:49 PM
  5. Need to Calculate Total Hours from 2 date cells and 2 time cells in military time.
    By psunursingguy21 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-07-2015, 06:46 PM
  6. Format time from military time to standard time
    By Valencia0307 in forum Excel General
    Replies: 7
    Last Post: 06-01-2014, 11:15 AM
  7. [SOLVED] How to get a total 'time in minutes' from a column that has military times entered
    By bustech in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2013, 07:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1