+ Reply to Thread
Results 1 to 7 of 7

Formula to convert military time into hh:mm format

  1. #1
    Registered User
    Join Date
    10-09-2015
    Location
    Miami
    MS-Off Ver
    Office 2010
    Posts
    3

    Formula to convert military time into hh:mm format

    Hello, I am looking for a formula that converts military time into hh:mm format (I only need hrs and min)

    For ex. If I type 600 in a cell I want the formula to automatically change it to 6:00 am, or if I write 1800 it changes to 18:00.

    I have looked for it before but haven't really found something that I can understand or that serves my purpose.

    I appreciate your help and responses!

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Formula to convert military time into hh:mm format

    Try

    =TEXT(A1,"00\:00")+0

  3. #3
    Forum Expert
    Join Date
    08-02-2013
    Location
    Québec
    MS-Off Ver
    Excel 2003, 2007, 2013
    Posts
    1,407

    Re: Formula to convert military time into hh:mm format

    Hello and welcome to the forum,

    Here's a formula you can try:
    Please Login or Register  to view this content.
    And then format you cell with a custom time format h:mm
    GC Excel - My blog

    If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Formula to convert military time into hh:mm format

    Hi
    Use this format
    #0":"00

  5. #5
    Registered User
    Join Date
    10-09-2015
    Location
    Miami
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Formula to convert military time into hh:mm format

    This one seemed to work the best, is there a way to add am/pm after??

  6. #6
    Registered User
    Join Date
    10-09-2015
    Location
    Miami
    MS-Off Ver
    Office 2010
    Posts
    3

    Re: Formula to convert military time into hh:mm format

    Quote Originally Posted by José Augusto View Post
    Hi
    Use this format
    #0":"00

    This one seemed to work the best, is there a way to add am/pm after?

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

    Re: Formula to convert military time into hh:mm format

    If you use that method, the cell value is unchanged, it just looks different. I suggest Jonmo's formula, then format the result as h:mm am/pm
    Entia non sunt multiplicanda sine necessitate

+ 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. [SOLVED] Formula Tracking Time In Military Format - Receiving Error
    By DDM64 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-22-2013, 05:24 AM
  2. Simple way to convert military time to standard where military has no colon
    By salvator in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-03-2011, 10:27 AM
  3. Convert non-military time to tenths of hours
    By kwray49 in forum Excel General
    Replies: 8
    Last Post: 08-17-2009, 08:00 AM
  4. [SOLVED] How to I convert standard time to Military or 24 hour format?
    By Nacho in forum Excel General
    Replies: 5
    Last Post: 06-28-2006, 02:20 PM
  5. Convert to Military time?
    By telewats in forum Excel General
    Replies: 2
    Last Post: 03-13-2006, 02:20 PM
  6. Convert data into standard military time format
    By geog in forum Excel General
    Replies: 2
    Last Post: 12-12-2005, 03:50 PM
  7. [SOLVED] Military Time:Is there a formula that would easily convert it?
    By Sue in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-02-2005, 01:06 PM
  8. [SOLVED] How do I convert Military Time to minutes?
    By Rachael in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-06-2005, 06:06 PM

Tags for this Thread

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