+ Reply to Thread
Results 1 to 5 of 5

Adding a colon ( : ) to convert a standard number into a time format

  1. #1
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    350

    Adding a colon ( : ) to convert a standard number into a time format

    Good afternoon,

    Is there a way to enter a colon into a standard number to create a value that can be formatted into a 24 hour time value ?

    eg a time is listed as 1345 with a general number format, and I want it returned as 13:45 witha custom format of hh:mm.

    Other than creating a table and using a vlookup function, I am hoping there is a better way?

    Darren
    Last edited by Grimace; 05-21-2009 at 09:26 PM.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,719

    Re: Adding a colon ( : ) to convert a standard number into a time format

    Say you have the time in column G, then, in column H:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Moderator DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Suffolk, UK
    MS-Off Ver
    2002, 2007 & 2010
    Posts
    21,520

    Re: Adding a colon ( : ) to convert a standard number into a time format

    If you simply want the 1345 to appear as 13:45 you can use a Custom Format of: 00\:00

    If as outlined on prior post you want the value to be physically coerced to a Time value in a subsequent column you can adapt the above concept such that:

    B1: =0+TEXT(A1,"00\:00")
    formatted as hh:mm

  4. #4
    Forum Contributor Grimace's Avatar
    Join Date
    05-04-2009
    Location
    Gold Coast, Australia
    MS-Off Ver
    Excel 2010
    Posts
    350

    Re: Adding a colon ( : ) to convert a standard number into a time format

    thank you .. solved the problem

  5. #5
    Registered User
    Join Date
    06-21-2013
    Location
    Arizona, U.S.
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Adding a colon ( : ) to convert a standard number into a time format

    This will also help if you have a range of cells in which you want to have it converted to Time, and noticed as time by Excel.

    Please Login or Register  to view this content.

+ 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