Calculating Hours Between 2 Dates & Removing Weekend Dates

[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

 

[Bucky]

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.

 

[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

 

[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)

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

 

[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)

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)

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