+ Reply to Thread
Results 1 to 8 of 8

Format time from military time to standard time

  1. #1
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Exclamation Format time from military time to standard time

    Hello,

    I want to be able to type in military time without having to use the ':' symbol and have it convert to standard time with AM/PM showing at the end (example, type "1400" and end up with "2:00 PM").

    Does anyone know how to do this?? I am fairly new to the whole excel thing so in more simpler terms would be appreciated! Thanks for your help,

    Valerie

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Format time from military time to standard time

    Excel doesn't know you are trying to enter a time, so it will never interpret 1400 as 2:00 PM. The only way to do this is to either add another column that provides a conversion

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or use a macro to do the conversion in the same cell.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Re: Format time from military time to standard time

    Thank you. Do you know how would I go about creating a Macros to do this?

  4. #4
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Re: Format time from military time to standard time

    I tried the formula you gave "=TIME((A1-MOD(A1,100))/100,MOD(A1,100),0)" but it just returned the value "12:00 AM"

  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: Format time from military time to standard time

    Another way:

    A
    B
    C
    1
    2217
    10:17 PM
    B1: =--TEXT(A1,"00\:00")
    2
    2215
    10:15 PM
    3
    1086
    11:26 AM
    4
    2200
    10:00 PM
    5
    1287
    1:27 PM
    6
    2152
    9:52 PM
    7
    1225
    12:25 PM
    8
    1725
    5:25 PM
    9
    529
    5:29 AM
    10
    683
    7:23 AM
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Format time from military time to standard time

    What did you put in A1??

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Format time from military time to standard time

    Quote Originally Posted by Valencia0307 View Post
    Do you know how would I go about creating a Macros to do this?
    See this:

    http://www.cpearson.com/Excel/DateTimeEntry.htm
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Format time from military time to standard time

    See attached for macro implementation specific to your question.
    Attached Files Attached Files

+ 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. Military time to standard time
    By rdeiseman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-11-2013, 04:35 PM
  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 Standard Time with AM/PM to Military Time
    By LonnieJo1 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-11-2009, 07:20 PM
  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 data into standard military time format
    By geog in forum Excel General
    Replies: 2
    Last Post: 12-12-2005, 03:50 PM

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