Calculating Hours Between 2 Dates & Removing Weekend Dates

D

dbennett

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
 
B

Bucky

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

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

Biff

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
 
B

Bob Phillips

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 said:
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

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
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
Top