+ Reply to Thread
Results 1 to 12 of 12

Converting hours and minutes in military time to minutes

  1. #1
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Converting hours and minutes in military time to minutes

    I have been banging my head over this for about a week and i think im over thinking it. This is what i need done.

    Im making a worksheet and need people to enter in a start time and an end time, then i need it to tell me how many hours that is as well as a total of how many minutes all together. Basically i need it to break down in minutes for sure.

    But it must have military time only, and if you do enter it in as time i don't want them to put in the colon. And my issue is usually if the time is 2200 to 0100, it gives me a negative, i fixed that but then my minutes are messed up. The hours and minutes can be separated or in the same cell.

    Right now i have it as Start hour / minutes
    End hour / minutes
    total hours / minutes
    total minutes all together

    Plz give me a hand i'd appreciate it.

    Any other questions you have for me, message me, i need this asap..plz

    thanks
    Last edited by Argile79; 04-13-2010 at 02:42 PM.

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

    Re: Excel Issues

    Welcom to the forum.

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Excel Issues

    And editing your e-mail address is an open invitation to spam...

  4. #4
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting hours and minutes in military time to minutes, appreciate it.

    Sorry, i was at work and just joined and needed an awnser didn't have the time to read, i did, again i apologize, new to any kinds of forums. Appreciate any help you guys may give me.

    Have a great day.

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

    Re: Converting hours and minutes in military time to minutes

    If A1 contains 1100 and B1 contains 0100, then

    =60 * (DOLLARDE(B1/100, 60) - DOLLARDE(A1/100, 60) + 24 * (B1+0 < A1+0) )

    ... returns 840, the difference in minutes

  6. #6
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting hours and minutes in military time to minutes

    what if A1 has 2300 and B1 has 0100, would that same formula work?

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

    Re: Converting hours and minutes in military time to minutes

    You tried it, and ...?

  8. #8
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting hours and minutes in military time to minutes

    nothing...it didn't work, all i got in my answer cell was #NAME?

    i typed in what you had, changed B1 TO F5 AND A1 TO F4, the only thing I changed to match my formula..i don't understand what im messing up. any other ideas?

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

    Re: Converting hours and minutes in military time to minutes

    Tools > Add-Ins, tick Analysis ToolPak

  10. #10
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting hours and minutes in military time to minutes

    nice you rock, thanks so much, appreciate it..now just need to tweak it a little bit, but i got a start. thanks again. I have another issue to, but il make another thread since its abotu dates.

    So 1 last thing while i got you, how can i get it to tell me total hours and mins before it go right to mins?

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

    Re: Converting hours and minutes in military time to minutes

    =(DOLLARDE(B1/100, 60) - DOLLARDE(A1/100, 60) ) / 24 + (B1+0 < A1+0)

    and format the result as [h]:mm
    Last edited by shg; 04-13-2010 at 02:46 PM. Reason: removed spurious parens

  12. #12
    Registered User
    Join Date
    04-13-2010
    Location
    Illinois
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Converting hours and minutes in military time to minutes

    thank you bud, appreciate it, works beautifully now...have a great day.

+ 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