Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..

Try this

=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result
required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..

Hi Bob,

Thanks for this, it worked a treat, I've justed realised I'll need to
exclude weekends, how would I implement this using networkdays?

Thanks in advance,

Mo..

"Bob Phillips" wrote:

Try this

=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result
required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..

=(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24

or

=(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24


if you want to exclude holidays

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi Bob,

Thanks for this, it worked a treat, I've justed realised I'll need to
exclude weekends, how would I implement this using networkdays?

Thanks in advance,

Mo..

"Bob Phillips" wrote:

Try this

=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result
required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..

Hi Bob,

thanks loads, both of them worked a treat.

Thanks again,

Mo..

"Bob Phillips" wrote:

=(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24

or

=(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24


if you want to exclude holidays

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi Bob,

Thanks for this, it worked a treat, I've justed realised I'll need to
exclude weekends, how would I implement this using networkdays?

Thanks in advance,

Mo..

"Bob Phillips" wrote:

Try this

=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual result
required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..

Pleasure Mo.

"Mohammed Zenuwah" wrote in
message ...
Hi Bob,

thanks loads, both of them worked a treat.

Thanks again,

Mo..

"Bob Phillips" wrote:

=(NETWORKDAYS(A1,A2)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24

or

=(NETWORKDAYS(A1,A2, holidays)-1)*8.5+(MOD(A2,1)-MOD(A1,1))*24


if you want to exclude holidays

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote in
message ...
Hi Bob,

Thanks for this, it worked a treat, I've justed realised I'll need to
exclude weekends, how would I implement this using networkdays?

Thanks in advance,

Mo..

"Bob Phillips" wrote:

Try this

=(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24

--
HTH

Bob Phillips

"Mohammed Zenuwah" wrote
in
message ...
Hi All,

I'm hoping someone can help me with the following problem.

I need to create a formula which calculates the different between
two
date/time values excluding out of business hours.

Here's an example.
A1: 21/06/2005 10:00:00
A2: 23/06/2005 14:30:00

When using "=(B1-A1)*24" I can the result 52.5
If the business hours are between 08:30 and 17:00 the actual
result
required
is 21.5

I hope this makes sense.

Thanks in advance,

Mo..