Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating elapsed days and hours
I figured out a formula for this but now I wonder if there would have been an
easier way. Here's the situation. I had four separate cells - Admit Date (A1), Admit Time (B1), Discharge Date (C1), Discharge time (D1). I needed to calculate the number of days and the number of hours/minutes spent in the hospital. (If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm - it was NOT 1 day - it was 0 days, 23 hours.) So here is my forumula. To calculate the number of days spent in the hospital: =IF(C2-A20,IF(D2B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0) To calculate the number of hours/minutes spent: =IF(D20,IF(B20,IF(D2B2,D2-B2,(D2-0)+(12-B2)),0),0) Was there an easier way?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating elapsed days and hours
=(C1-A1) +(D1-B1) and format cell as dd:hh:mm to give time as days:hours:minutes In your example, reslt would be 00:23:00 Does that help? "LauriS" wrote: I figured out a formula for this but now I wonder if there would have been an easier way. Here's the situation. I had four separate cells - Admit Date (A1), Admit Time (B1), Discharge Date (C1), Discharge time (D1). I needed to calculate the number of days and the number of hours/minutes spent in the hospital. (If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm - it was NOT 1 day - it was 0 days, 23 hours.) So here is my forumula. To calculate the number of days spent in the hospital: =IF(C2-A20,IF(D2B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0) To calculate the number of hours/minutes spent: =IF(D20,IF(B20,IF(D2B2,D2-B2,(D2-0)+(12-B2)),0),0) Was there an easier way?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating elapsed days and hours
or put the SAME formula in two cells, and format one as "dd" and the other as
"hh:mm" "Toppers" wrote: =(C1-A1) +(D1-B1) and format cell as dd:hh:mm to give time as days:hours:minutes In your example, reslt would be 00:23:00 Does that help? "LauriS" wrote: I figured out a formula for this but now I wonder if there would have been an easier way. Here's the situation. I had four separate cells - Admit Date (A1), Admit Time (B1), Discharge Date (C1), Discharge time (D1). I needed to calculate the number of days and the number of hours/minutes spent in the hospital. (If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm - it was NOT 1 day - it was 0 days, 23 hours.) So here is my forumula. To calculate the number of days spent in the hospital: =IF(C2-A20,IF(D2B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0) To calculate the number of hours/minutes spent: =IF(D20,IF(B20,IF(D2B2,D2-B2,(D2-0)+(12-B2)),0),0) Was there an easier way?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating elapsed days and hours
Two problems with that. They wanted the days in a separate column from the
hours/minutes. And the data I had to work with, some of the date fields also had times entered in them - some didn't. So I couldn't just subtract the values in those two fields - the result wasn't reliable. But, for future reference, it's a good way to do it. Thanks! Lauri "Toppers" wrote: =(C1-A1) +(D1-B1) and format cell as dd:hh:mm to give time as days:hours:minutes In your example, reslt would be 00:23:00 Does that help? "LauriS" wrote: I figured out a formula for this but now I wonder if there would have been an easier way. Here's the situation. I had four separate cells - Admit Date (A1), Admit Time (B1), Discharge Date (C1), Discharge time (D1). I needed to calculate the number of days and the number of hours/minutes spent in the hospital. (If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm - it was NOT 1 day - it was 0 days, 23 hours.) So here is my forumula. To calculate the number of days spent in the hospital: =IF(C2-A20,IF(D2B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0) To calculate the number of hours/minutes spent: =IF(D20,IF(B20,IF(D2B2,D2-B2,(D2-0)+(12-B2)),0),0) Was there an easier way?? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculating elapsed days and hours
To ignore times in the date fields use:
=(INT($C1)-INT($A1))+($D1-$B1) Or if time fields are zero, i.e. times are in date fields: =IF(AND($B2=0,$D2=0),$C2-$A2,(INT($C2)-INT($A2))+($D2-$B2)) Place the same formula in say cells E1 and F1 Format E1 as custom==dd and F1 as Custom==hh:mm HTH "LauriS" wrote: Two problems with that. They wanted the days in a separate column from the hours/minutes. And the data I had to work with, some of the date fields also had times entered in them - some didn't. So I couldn't just subtract the values in those two fields - the result wasn't reliable. But, for future reference, it's a good way to do it. Thanks! Lauri "Toppers" wrote: =(C1-A1) +(D1-B1) and format cell as dd:hh:mm to give time as days:hours:minutes In your example, reslt would be 00:23:00 Does that help? "LauriS" wrote: I figured out a formula for this but now I wonder if there would have been an easier way. Here's the situation. I had four separate cells - Admit Date (A1), Admit Time (B1), Discharge Date (C1), Discharge time (D1). I needed to calculate the number of days and the number of hours/minutes spent in the hospital. (If a person checked in on 8/1 at 3:00 pm and checked out on 8/2 at 2:00 pm - it was NOT 1 day - it was 0 days, 23 hours.) So here is my forumula. To calculate the number of days spent in the hospital: =IF(C2-A20,IF(D2B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0) To calculate the number of hours/minutes spent: =IF(D20,IF(B20,IF(D2B2,D2-B2,(D2-0)+(12-B2)),0),0) Was there an easier way?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating number of hours accross days | Excel Worksheet Functions | |||
How to convert an elapsed time in minutes to Days hours and minute | Excel Discussion (Misc queries) | |||
Calculating number of hours between two days | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |