Time formats

**tarsuc** · 01-21-2011, 11:24 PM

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.

**zbor** · 01-22-2011, 04:08 AM

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

**DonkeyOte** · 01-22-2011, 04:42 AM

@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.

**Ron Coderre** · 01-22-2011, 09:02 AM

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?

**DonkeyOte** · 01-22-2011, 09:23 AM

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.

Time formats

LinkBack

Thread Tools

Rate This Thread

Display

Time formats

Re: Time formats

Re: Time formats

Re: Time formats

Re: Time formats

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions