+ Reply to Thread
Results 1 to 9 of 9

how to convert a cell to become a time format

  1. #1
    Forum Contributor
    Join Date
    09-19-2007
    Location
    Beirut
    MS-Off Ver
    0365 MSO Version 2109
    Posts
    207

    how to convert a cell to become a time format

    Hello all,

    i got under A worng time format showing as

    736
    1645

    actualy this original data is numeric and not a time format at all

    is there a way to convert so under B wil show as

    07:36:am
    16:45:pm

    thanks

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: how to convert a cell to become a time format

    Use another column with this formula

    =TIME(B1/100,A1-INT(B1/100)*100,0)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: how to convert a cell to become a time format

    Chip Pearson has a quick time entry method

    http://www.cpearson.com/excel/DateTimeEntry.htm
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  4. #4
    Registered User
    Join Date
    03-08-2012
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: how to convert a cell to become a time format

    i need help with the date time stamp:
    when i type data in A2 i want B2 to automatically add in the time and the date, ive gotten this to work - only problem is when i drag the formula down and i type info in A3 etc it changes all the dates and times to the current date and time, i need each date and time to be different and to be as per what was filled into A2, A3 etc and not change.... please help

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: how to convert a cell to become a time format

    Start a new thread, don't hijack someone else's.

  6. #6
    Forum Contributor
    Join Date
    09-19-2007
    Location
    Beirut
    MS-Off Ver
    0365 MSO Version 2109
    Posts
    207

    Re: how to convert a cell to become a time format

    did not work i always get 12:00:00 in B

  7. #7
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: how to convert a cell to become a time format

    Try using this

    =TIME(MID(A2,1,LEN(A2)-2),RIGHT(A2,2),0)
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: how to convert a cell to become a time format

    In B1, then copy down

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

    Format as time.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  9. #9
    Forum Contributor
    Join Date
    09-19-2007
    Location
    Beirut
    MS-Off Ver
    0365 MSO Version 2109
    Posts
    207

    Re: how to convert a cell to become a time format

    @ DGagnon
    @ Haseeb A


    thanks all it worked great.

+ 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