Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
Hi,
I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
See if this does what you want.
As long as the dates are true Excel dates then this should work. Let's assume the dates are in the range A1:A10. To count the number of dates for a specific month: =SUMPRODUCT(--(MONTH(A1:A10)=n)) Where n = the month number of interest. 1 = January to 12 = December Note that empty cells will evaluate as month 1 (January). To account for that when counting for Janauary: =SUMPRODUCT(--(A1:A10<""),--(MONTH(A1:A10)=1)) -- Biff Microsoft Excel MVP "Rubem" wrote in message ... Hi, I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
Ruben
In another column place the formula =Month(A1) where A1 is the first cell in the column that holds the dates. Drag that formula down with the fill handle. Then count the number of 1's. 2's, etc. HTH Otto "Rubem" wrote in message ... Hi, I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
If you're doing this on a monthly basis, I would think that the year would be
important, too. =sumproduct(--(text(a1:a10,"yyyymm")="201001")) This still expects your values to be real dates -- I'm hoping that you just formatted the cells to display mm/yy. Adjust the ranges to match--but you can't use whole columns (except in xl2007+). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Rubem wrote: Hi, I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
Assume that your Dates are in A1 to A100 and you would like to get the count
of dates for the month of January 2010. =SUMPRODUCT((A1:A100=DATE(2010,1,1))*(A1:A100<=DA TE(2010,1,31))) Change the cell Range A1: A100 to your desired Range, if required. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rubem" wrote: Hi, I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date without true value
Assume that your Dates are in A1 to A100 and you would like to get the count
of dates for the month of January 2010. =SUMPRODUCT((A1:A100=DATE(2010,1,1))*(A1:A100<=DA TE(2010,1,31))) Change the cell Range A1: A100 to your desired Range, if required. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Rubem" wrote: Hi, I have a table that lists the invoice dates. On a monthly basis, I need to write a report of how many invoices were issue, let's say, in January. I converted the date mm/dd/yy into just mm/yy, but I still have the true value in the background and when I try to count how many invoices were issued in January, I get "0". Any suggestion? Thank you for your help. Rubem |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert a date that does not update if a condition is true | Excel Worksheet Functions | |||
convert a text date to a true date | Excel Discussion (Misc queries) | |||
return TRUE if a date falls between two dates | Excel Worksheet Functions | |||
If Function Value if true = Date +30days | Excel Discussion (Misc queries) | |||
Need true or false if a date falls between a date range | Excel Worksheet Functions |