+ Reply to Thread
Results 1 to 4 of 4

Military/24 Hour Time Adding and difference finding

  1. #1
    Registered User
    Join Date
    01-24-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    31

    Military/24 Hour Time Adding and difference finding

    I have a spreadsheet with a lot of raw data that I need to separate and make statistics with.

    Basic columns are: Name, Event, Time Start, Time End

    I need to see how long someone was at an event. I can do this with simple math, but the problem comes that I am using 24 hour clock to do this. The times only come in 30 minute intervals. I have two problems...

    1) I need to add the total time someone has been at all of the events.

    2) I need to know how long they have been at one event first, including if it starts at 1700 and goes until 0200.

    An example spreadsheet is below.





    Worth noting is that there are consistently more rows being added by the day or week.
    Thanks.
    Attached Files Attached Files
    Last edited by Squirrel; 08-29-2011 at 08:57 PM.

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Military/24 Hour Time Adding and difference finding

    In E2 and copy down,

    =(DOLLARDE(D2/100, 60) - DOLLARDE(C2/100, 60))/24 + (C2>D2)

    Format the result as [h]:mm
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    01-24-2011
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    31

    Re: Military/24 Hour Time Adding and difference finding

    Thanks a lot.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,658

    Re: Military/24 Hour Time Adding and difference finding

    You're welcome, glad it worked for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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