Calculating Hours Between 2 Dates & Removing Weekend Dates

I have an application that I need to track the hours between 2 date /
times.

A1 10/26/05 13:00
A2 10/28/05 15:00

=A2-A1 50:00 (hh:mm)

So far okay. However the time difference needs to subtract the 48 hours
for the weekend days if the range includes these.

Any thoughts other than manual?

Thanks

[email protected] wrote:
> So far okay. However the time difference needs to subtract the 48 hours
> for the weekend days if the range includes these.

Take a look at the function NETWORKDAYS. You will probably need to
install the Analysis Toolpak.

Hi!

One way:

=A2-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(A2))),2)>5))

Format the cell as [hh]:mm

Will either the start or end be on a weekend? What happens then?

Biff

<[email protected]> wrote in message
news:[email protected]...
>I have an application that I need to track the hours between 2 date /
> times.
>
> A1 10/26/05 13:00
> A2 10/28/05 15:00
>
> =A2-A1 50:00 (hh:mm)
>
> So far okay. However the time difference needs to subtract the 48 hours
> for the weekend days if the range includes these.
>
> Any thoughts other than manual?
>
> Thanks
>

An alternative that caters for starting/ending on a weekend day

=networkdays(A1,A2)*24+HOUR(A2)*(WEEKDAY(A2,2)<6)-HOUR(A1)*(WEEKDAY(A1,2)<6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> One way:
>
> =A2-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(A2))),2)>5))
>
> Format the cell as [hh]:mm
>
> Will either the start or end be on a weekend? What happens then?
>
> Biff
>
> <[email protected]> wrote in message
> news:[email protected]...
> >I have an application that I need to track the hours between 2 date /
> > times.
> >
> > A1 10/26/05 13:00
> > A2 10/28/05 15:00
> >
> > =A2-A1 50:00 (hh:mm)
> >
> > So far okay. However the time difference needs to subtract the 48 hours
> > for the weekend days if the range includes these.
> >
> > Any thoughts other than manual?
> >
> > Thanks
> >
>
>

Correction

=(networkdays(A1,A2)-1)*24+HOUR(A2)*(WEEKDAY(A2,2)<6)-HOUR(A1)*(WEEKDAY(A1,2
)<6)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> An alternative that caters for starting/ending on a weekend day
>
>
=networkdays(A1,A2)*24+HOUR(A2)*(WEEKDAY(A2,2)<6)-HOUR(A1)*(WEEKDAY(A1,2)<6)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
> > Hi!
> >
> > One way:
> >
> > =A2-A1-SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(A1)&":"&INT(A2))),2)>5))
> >
> > Format the cell as [hh]:mm
> >
> > Will either the start or end be on a weekend? What happens then?
> >
> > Biff
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > >I have an application that I need to track the hours between 2 date /
> > > times.
> > >
> > > A1 10/26/05 13:00
> > > A2 10/28/05 15:00
> > >
> > > =A2-A1 50:00 (hh:mm)
> > >
> > > So far okay. However the time difference needs to subtract the 48
hours
> > > for the weekend days if the range includes these.
> > >
> > > Any thoughts other than manual?
> > >
> > > Thanks
> > >
> >
> >
>
>