Excel formula help

[medicnick83]

Hi guys,

I have started logging my patient hours on the ambulance.
I hate paper work! FACT!

I need help with something if possible.

I need to take 1 time, put in another time, and get the difference.

So, if Cell A has 19:40 (when we got the call) and then dropped the patient off at the hospital at 20:34 (Cell B) I need it to work out the difference which is 54 minutes.

Anyone good with Excel organize this for me?

 

[Boomstomp]

convert 19:40 to minutes (19*60 + 40) and convert 20:34 to minutes (20*60+34). Now subtract the former from the latter.

 

[bwana]

Just put "19:40" in Cell A1 and "20:34" in cell B1 and in cell C1 "=SUM(B1-A1)"

EDIT - the only problem I foresee with this method is if you work past midnight . . . so dont.

 

[KobusDJ]

To make provision for the "start before and end after midnight", try this......

=(IF(A1 > B1, (B1 + 1) - A1, B1 - A1)) * 24 * 60

Answer is in minutes.

 

[medicnick83]

Just put "19:40" in Cell A1 and "20:34" in cell B1 and in cell C1 "=SUM(B1-A1)"

EDIT - the only problem I foresee with this method is if you work past midnight . . . so dont.

Hahahaha... I wish!

We did 6 calls last night, 4 before midnight and 2 after midnight

To make provision for the "start before and end after midnight", try this......

=(IF(A1 > B1, (B1 + 1) - A1, B1 - A1)) * 24 * 60

Answer is in minutes.

Nice!

 

[medicnick83]

Link

Hi Guys,

Check here: http://www.quickshare.co.za/files/z3in9cik/Example.xls.html

I've included the formula that KobusDJ gave me.

Anything else you guys think I should add?

Was thinking of adding a "total hours" at the bottom, but it should add in "x hours x minutes" type setup.

 

[KobusDJ]

To get the total......

=SUM(E6:E11)/24/60

In your example you will then have 0.215972222 (a fraction of a day)

To see it in hours:minutes, format the cell as time, and you will have 05:11:00 AM or custom with type as hh:mm and you will see 05:11 (same format as your TRIP TIMES)

Example.xls

EDIT: If you want the hh:mm look throughout, you could drop the x 24 x 60 (and / 24 / 60 for the sum), and just format all the time columns as hh:mm

It will then look like this : Example2.xls