# Time formats

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. ## Re: Time formats

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

3. ## 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. ## 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?

5. ## 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.

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