+ Reply to Thread
Results 1 to 5 of 5

Time formats

  1. #1
    Registered User
    Join Date
    01-21-2011
    Location
    UK
    MS-Off Ver
    Excel 2003
    Posts
    1

    Time formats

    Hi there,

    I hope you can help me.

    Im a doc working on a research paper,and there are 2 columns (900 fields in each) of time that i have, whose difference i need to find- how many hours the patients took to reach ER.

    So
    the format in which the time is written is HH or HH.MM.
    Eg: 7:30 am is 7.30, 11 am is 11, 3:30 pm is 15.30 etc.

    1) its becoming very difficult for me to add a 00 to all times written as HH in the column. Is there a specific formula i can use to cnvert all HH into HH:MM, and all HH.MM into HH:MM. I tried changing the format of the whole column, but it shows bizarre results which i have no idea how they got.

    2) Secondly and lastly, to find the time difference between the second column and 1st column, is there a formula i can use? Especially if the first column shows a time of 22:30 hrs and second column shows a time of 01:30 hours the next day.

    I hope you can clear this doubt for me. Ive been typing for the last 2 days.

    Thanx.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,406

    Re: Time formats

    I'm not sure I follow... Can you uplad example workbook with few inputs and desired outputs?

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Time formats

    @tarsuc, if the values are as you say they are then:

    Please Login or Register  to view this content.
    should work without issue (even with the likes of 11 am etc...)

    If you get errors with the above apply TRIM to B1 & A1 ... failing that post a sample file.

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Time formats

    I think I understand what you want to do....

    You would prefer to enter times (hours and minutes) with a decimal separator, instead of a colon.

    So 15.45 would mean 3:45 PM

    If that's true, try something like this:
    A1: start time....eg 11 (meaning 11 AM)
    B1: end time.....eg 15.45 (meaning 3:45 PM)

    This formula calculates the difference, in hours and minutes, between those 2 times
    Please Login or Register  to view this content.
    Format that cell as time (hrs:mm)

    In the above example, the formula returns: 4:45

    Here are some other examples:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Time formats

    Ah, good spot Ron - I missed that completely

    On that basis - another alternative would be:

    Please Login or Register  to view this content.
    useful perhaps should you wish to use pre XL2007 and avoid ATP dependency.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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