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-A2>0,IF(D2>B2,DATEDIF(A2, C2, "D"),IF(D2=B2,C2-A2,C2-A2-1)),0)
> > >
> > > To calculate the number of hours/minutes spent:
> > >
> > > =IF(D2>0,IF(B2>0,IF(D2>B2,D2-B2,(D2-0)+(12-B2)),0),0)
> > >
> > > Was there an easier way??
|
Similar Threads
