Functions & Formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

I am working an a s/sheet which amongst other things shows 2 dates, the
first one is the date a call was opened & the second one is a date the call
was closed. I have to calculate the number of days each call was open for
(excluding weekends) to get my average. Now, I could do this individually but
there are approx 5000 individual calls so I would really appreciate it if
anyone could help with this one?
 
Hi,

Try usingn Networkdays function.The syntax is

= NETWORKDAYS(start_date,end_date,holidays)

Holidays is an optional range of one or more dates to exclude from the
working calendar, such as state and federal holidays and floating holidays

However make sure you have the Analysis Toolpak installed before using
this function.

Regards

Govind.
 
You can use the NETWORKDAYS function, it's part of the Analysis ToolPak that
comes with excel

=NETWORKDAYS(start_date,end_date,holidays)

where holidays can be a named range or a range where you would put public
holidays

=NETWORKDAYS(A1,B1,C2:C12)

as an example

Regards,

Peo Sjoblom
 
Did you ever get an answer? I have been looking for this for years also but
also need to break down the average into quarterly averages. I have been
given the following formulas to try =AVERAGE(IF(INT(MONTH(C:C)+2/3)=4
but they don't compute correctly for some strange reason. If you did find a
solution, please I still need to know.
Thank you
Kathi
 
Hi Kathi

I think you have only posted part of the formula. There must be some
other values to be averaged, if the date happens to fall in the 4th
quarter.
Maybe something like the following array entered formula

{=AVERAGE(IF(INT((MONTH(C1:C1000)+2)/3)=4,D1:D1000))}

Not this is an array formula, so it must be committed or edited using
Ctrl+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } around the
whole formula. Do not type them yourself.
You cannot use whole columns for this formula, unless every cell is
filled, otherwise you will get a #NUM error.

Suzie

Once again, I am not seeing the whole of this thread so don't know
whether you have already had an answer.
With Open date in A1 and Close date in B1, enter in C1
=NETWORKDAYS(A1,B1)
then carry out your average of the data in column C.

If you want to exclude holiday dates as well as weekends, then use the
optional additional parameter
=NETWORKDAYS(A1,B1,holidays)
where holidays can either be a named range containing public holiday
dates, or a range of cells e.g. $G$1:$G$9 which hold the holiday dates.
 
Roger Govier said:
Hi Kathi

I think you have only posted part of the formula. There must be some
other values to be averaged, if the date happens to fall in the 4th
quarter.
Maybe something like the following array entered formula

{=AVERAGE(IF(INT((MONTH(C1:C1000)+2)/3)=4,D1:D1000))}

Not this is an array formula, so it must be committed or edited using
Ctrl+Shift+Enter (CSE) not just Enter.
When you use CSE, Excel will insert the curly braces { } around the
whole formula. Do not type them yourself.
You cannot use whole columns for this formula, unless every cell is
filled, otherwise you will get a #NUM error.

Suzie

Once again, I am not seeing the whole of this thread so don't know
whether you have already had an answer.
With Open date in A1 and Close date in B1, enter in C1
=NETWORKDAYS(A1,B1)
then carry out your average of the data in column C.

If you want to exclude holiday dates as well as weekends, then use the
optional additional parameter
=NETWORKDAYS(A1,B1,holidays)
where holidays can either be a named range containing public holiday
dates, or a range of cells e.g. $G$1:$G$9 which hold the holiday dates.
Click to expand...
 
PEO Sjoblom,

I would like to ask if you can help me with functions/formulas for finding
the # of days between specified dates (yyyy/mm/dd) to (yyyy/mm/dd)
(2008,02,08 to 2008,05,10)

Can you help me?

Thanks
Erica
 
I'm not Peo but try............

=Laterdate-earlierdate returns 64 for me.


Gord Dibben MS Excel MVP
 
Back
Top