| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula to assist with counting dates
Could somebody please assist with a formula to count the number of dates in
February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Hi,
Put this in a cell and drag dwom 11 rows to get a monthly count for each month of 2009 =SUMPRODUCT(--(MONTH($A$1:$A$30)=ROW(A1)),--(YEAR($A$1:$A$30)=2009)) Change range to suit. Mike "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Suppose you have dates in ColA, number of dates in February is .
=SUMPRODUCT(--(MONTH(A1:A1000)=2)) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Missed out the year
=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose you have dates in ColA, number of dates in February is . =SUMPRODUCT(--(MONTH(A1:A1000)=2)) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
OR
=SUMPRODUCT(--(TEXT(A1:A1000,"mmyyyy")="022009")) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
another way:
=SUM(IF((MONTH(A1:A100)=2)*(YEAR(A1:A100)=2009),1, ) this is an array formula so CTRL+SHIFT+ENTER it instead of simply using ENTER adjust yr ranges to suit On 8 Maj, 12:07, Dilly wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Is it possible to utilise this formula, however, by just highlighting the
column column as the range, as opposed to physically highlighting from A1-A1000. If i use this folrmula and highlight column A as opposed to A1:A1000 i get a NUM error Thanks in advance Mark "Jacob Skaria" wrote: Missed out the year =SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose you have dates in ColA, number of dates in February is . =SUMPRODUCT(--(MONTH(A1:A1000)=2)) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Is it possible to utilise the formula below , however, by just highlighting
the column as the range, as opposed to physically highlighting from A1-A1000. If i use this folrmula and highlight column A as opposed to A1:A1000 i get a NUM error =SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009)) "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
Sorry i didnt get you. Could you please explain further
-- If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Is it possible to utilise this formula, however, by just highlighting the column column as the range, as opposed to physically highlighting from A1-A1000. If i use this folrmula and highlight column A as opposed to A1:A1000 i get a NUM error Thanks in advance Mark "Jacob Skaria" wrote: Missed out the year =SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose you have dates in ColA, number of dates in February is . =SUMPRODUCT(--(MONTH(A1:A1000)=2)) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula to assist with counting dates
You will have to remove the headers and other text data to use this formula.
or other wise this will return an error... If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Is it possible to utilise this formula, however, by just highlighting the column column as the range, as opposed to physically highlighting from A1-A1000. If i use this folrmula and highlight column A as opposed to A1:A1000 i get a NUM error Thanks in advance Mark "Jacob Skaria" wrote: Missed out the year =SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009)) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Suppose you have dates in ColA, number of dates in February is . =SUMPRODUCT(--(MONTH(A1:A1000)=2)) If this post helps click Yes --------------- Jacob Skaria "Dilly" wrote: Could somebody please assist with a formula to count the number of dates in February 2009 for example, from a "long" list of random dates. We have a helpdesk function that logs calls and am trying to establish how many calls we received in February 2009 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need Assist With Formula Creation | Excel Worksheet Functions | |||
| Assist with Match Dates | Excel Discussion (Misc queries) | |||
| Formula Assist | Excel Discussion (Misc queries) | |||
| Rosters - is there a function or formula to assist with this | Excel Discussion (Misc queries) | |||
| Making Array Formula, please assist! | Excel Worksheet Functions | |||
Linear Mode
