Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Calculating networkdays & working hours

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Calculating networkdays & working hours

On Tue, 7 Aug 2007 19:20:03 -0700, Scopar
wrote:

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott


Most likely you need to format your answer as:

Format/Cells/Number/Custom Type: [h]:mm:ss


--ron
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 16
Default Calculating networkdays & working hours

Thank you!

I'm a little red faced now :o) I can't believe it was that simple ...

"Ron Rosenfeld" wrote:

On Tue, 7 Aug 2007 19:20:03 -0700, Scopar
wrote:

Hi,

Some time back, someone with much more Excel knowledge than I have posted a
formula on this site to help me work out the number of working hours between
2 dates/times - the time an e-mail was received and then answered.

The formula I'm using is:
=(NETWORKDAYS(A2,B2)-1)*(I$3-I$2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),I$3,I$ 2),I$3)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),I$3,I$2)

A2 = date/time received
B2 = date/time answered
I3 = end of business day
I2 = start of business day

I've come across a situation that this formula doesn't seem to like and that
is if the response date is more than 2 business days after the receipt date.

The example I have is:

Received: 27/03/2007 12:08:00 PM
Answered: 30/03/2007 11:42:51 AM

This returns a result of 01:04:51 work hours (no publc holidays).

If I change the received date to 28/03/2007 12:08:00 PM, I receive a result
of 16:34:51 which is correct.

Can someone help with this as I don't understand the original formula well
enough to work on an enhancement? :o)

Any help you can offer is much appreciated.

Scott


Most likely you need to format your answer as:

Format/Cells/Number/Custom Type: [h]:mm:ss


--ron

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 5,651
Default Calculating networkdays & working hours

On Tue, 7 Aug 2007 21:44:10 -0700, Scopar
wrote:

Thank you!

I'm a little red faced now :o) I can't believe it was that simple ...


Well, I'm glad the fix was that easy. It is a common mistake to make.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating time increments from dates during working hours S Davis Excel Worksheet Functions 0 October 24th 06 03:32 PM
Calculating Networkdays and Business Hours James A. Excel Worksheet Functions 3 September 26th 06 08:02 PM
Calculating working hours John Excel Worksheet Functions 3 July 1st 05 05:44 AM
Calculating working hours Mohammed Zenuwah Excel Discussion (Misc queries) 5 June 29th 05 10:52 AM
Calculating working hours Sarah Excel Discussion (Misc queries) 3 March 14th 05 12:42 AM


All times are GMT +1. The time now is 12:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"