Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
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 -- Kind regards Ann Shaw |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
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. In article , Annie wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
Many many many thanks
Much appreciated!!! -- Kind regards Ann Shaw "JE McGimpsey" wrote: 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. In article , Annie wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
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 -- Kind regards Ann Shaw "JE McGimpsey" wrote: 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. In article , Annie wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
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) "Annie" wrote in message ... 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 -- Kind regards Ann Shaw "JE McGimpsey" wrote: 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. In article , Annie wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
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 a1a2 would be false and produce a 0 and that you be added to the sum ie not adjusting it for the next day. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Annie" wrote: 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 -- Kind regards Ann Shaw "JE McGimpsey" wrote: 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. In article , Annie wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Times in Excel
If you are not comfortable with Boolean, you can try this:-
=24*(IF(A1A2,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 "Annie" wrote in message ... 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 -- Kind regards Ann Shaw "JE McGimpsey" wrote: 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. In article , Annie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
A new Excel add-ins web-site | Excel Discussion (Misc queries) | |||
Entering Times In Excel | Excel Discussion (Misc queries) | |||
Adding/Averaging Times in Excel | Excel Discussion (Misc queries) | |||
How do I duplicate a sheet 20 times in an excel spreadsheet | Excel Worksheet Functions |