Times in Excel

[Guest]

Hi
I am trying to calculate number of hours between 2 times. I am helping
someone in a hospital. If a patient checks in at 18:00pm and checks out at
03:30am - how can I calculate the number of hours they were in the hospital.
It will work within a 12 hour period for me but when it goes past midnight it
won't work- I am just deducting one time from another - is there a different
way???

Many thanks

 

[JE McGimpsey]

One way:

A1: 18:00
A2: 03:30

A3: =MOD(A2-A1,1)

alternatively

A3: =A2-A1+(A2<A1)

both of these work because XL stores times as fractional days. Since
03:30 (= 3.5/24) is less than 18:00 (=18/24) you need to add 1 (i.e., 24
hours). XL converts the boolean TRUE/FALSE of (A2<A1) into 1/0,
respectively.

 

[Guest]

Many many many thanks

Much appreciated!!!

 

[Guest]

Can I ask one other little tiny question - why use the > sign I'm just a
little confused and I need to be able to explain why I am using this
parameter.

Many thanks

 

[Bob Phillips]

That is just checking whether A2 is less than A1, which will give TRUE if
so, and the + will add 1 on if TRUE (number + TRUE = number + 1). This
caters for times spanning midnight by adding 1 to the negative value (2:30 -
18:00 is -15:30, add 1 (24 hours) and you get 8:30).

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi,

Excel stores times as factions of a day.

ie 12 midday is 0.5

So JEMcG was checking if the second time was before the first time and if it
was it would produce a true (which is a 1 value) and adding the 1 on to the
time would allow you to deduct the start time from the end time.

If it was after if 7am->7pm then the a1>a2 would be false and produce a 0
and that you be added to the sum ie not adjusting it for the next day.

 

[Epinn]

If you are not comfortable with Boolean, you can try this:-

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

Format the cell as General or Decimal.



Source: http://www.cpearson.com/excel/datearith.htm



=24*(A2-A1+(A2<A1))

=24*(MOD(A2-A1,1))



All three formulae return 9.5.



I have known the MOD formula for a long time but still don't understand it.



Experts, please feel free to correct me.



Epinn


Can I ask one other little tiny question - why use the > sign I'm just a
little confused and I need to be able to explain why I am using this
parameter.

Many thanks