Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Blanks in a list of dates by month
Hi,
I have a spreadsheet set up in the following way A B C E f ------etc 2 Start Date End Date Apr05 May05 Jun05 Jul05 ----etc 3 1 2 2 2 4 12/03/05 15/05/05 5 15/05/05 6 17/04/05 19/04/05 7 18/04/05 What i want to do is to identify by month a count of any entries that havent been closed. i though the easiest way would be to check for an entry in the end date column, if it exists then do nothing. If there is no entry then, check the start date and add 1 to the count in each following month upto today(). For some reason however i cannt get it to work it doesnt seem to recognise "=""" Any help would be appreciated rgds Paul |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Blanks in a list of dates by month
In C2
=SUMPRODUCT(--(MONTH($A$2:$A$20)=MONTH(C$1)),--(YEAR($A$2:$A$20)=YEAR(C$1)),--($B$2:$B$20="")) and copy across -- --- HTH Bob (change the xxxx to gmail if mailing direct) "phocused" wrote in message ... Hi, I have a spreadsheet set up in the following way A B C E f ------etc 2 Start Date End Date Apr05 May05 Jun05 Jul05 ----etc 3 1 2 2 2 4 12/03/05 15/05/05 5 15/05/05 6 17/04/05 19/04/05 7 18/04/05 What i want to do is to identify by month a count of any entries that havent been closed. i though the easiest way would be to check for an entry in the end date column, if it exists then do nothing. If there is no entry then, check the start date and add 1 to the count in each following month upto today(). For some reason however i cannt get it to work it doesnt seem to recognise "=""" Any help would be appreciated rgds Paul |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Blanks in a list of dates by month
Bob, worked a dream, thanks, I may need to rethink how i do this however.
Having applied your formula I realised that it records the data as required however once i enter an end date in B2 all data for the previous months and zero it. This means i end up with no historic data telling me how many calls were opened by month. i'll have a thnk about it. but again Many thanks for your help. Rgds Paul "Bob Phillips" wrote: In C2 =SUMPRODUCT(--(MONTH($A$2:$A$20)=MONTH(C$1)),--(YEAR($A$2:$A$20)=YEAR(C$1)),--($B$2:$B$20="")) and copy across -- --- HTH Bob (change the xxxx to gmail if mailing direct) "phocused" wrote in message ... Hi, I have a spreadsheet set up in the following way A B C E f ------etc 2 Start Date End Date Apr05 May05 Jun05 Jul05 ----etc 3 1 2 2 2 4 12/03/05 15/05/05 5 15/05/05 6 17/04/05 19/04/05 7 18/04/05 What i want to do is to identify by month a count of any entries that havent been closed. i though the easiest way would be to check for an entry in the end date column, if it exists then do nothing. If there is no entry then, check the start date and add 1 to the count in each following month upto today(). For some reason however i cannt get it to work it doesnt seem to recognise "=""" Any help would be appreciated rgds Paul |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Blanks in a list of dates by month
Hi,
Can anyone help with this? I still think it can be done... I just need some Genius to show me how:-) Heres the problem, in the question below you will see what i am trying to acheive. I want to count by day/month (see Bobs formula) the number of entries that are still open at the end of each month, in other words the end date is not set. However when the end date DOES get set, I want to retain the retain the historcal date. Is there any way to have my pie and eat it? Ref Start End Jan Feb Mar Apr......... 12321 23/01/06 1 1 1 then when the end date is added i still want the Jan - Mar data retaining Ref Start End Jan Feb Mar Apr May 12321 23/01/06 23/04/06 1 1 1 0 0 At the moment it fills Jan Feb and Mar butr as soon as I add the End date it Zeros the historical data. Rgds Paul "phocused" wrote: Bob, worked a dream, thanks, I may need to rethink how i do this however. Having applied your formula I realised that it records the data as required however once i enter an end date in B2 all data for the previous months and zero it. This means i end up with no historic data telling me how many calls were opened by month. i'll have a thnk about it. but again Many thanks for your help. Rgds Paul "Bob Phillips" wrote: In C2 =SUMPRODUCT(--(MONTH($A$2:$A$20)=MONTH(C$1)),--(YEAR($A$2:$A$20)=YEAR(C$1)),--($B$2:$B$20="")) and copy across -- --- HTH Bob (change the xxxx to gmail if mailing direct) "phocused" wrote in message ... Hi, I have a spreadsheet set up in the following way A B C E f ------etc 2 Start Date End Date Apr05 May05 Jun05 Jul05 ----etc 3 1 2 2 2 4 12/03/05 15/05/05 5 15/05/05 6 17/04/05 19/04/05 7 18/04/05 What i want to do is to identify by month a count of any entries that havent been closed. i though the easiest way would be to check for an entry in the end date column, if it exists then do nothing. If there is no entry then, check the start date and add 1 to the count in each following month upto today(). For some reason however i cannt get it to work it doesnt seem to recognise "=""" Any help would be appreciated rgds Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
auto updating list | Excel Worksheet Functions | |||
recognizing dates past End of Month | Excel Discussion (Misc queries) | |||
How to choose from a list that includes blanks? | Excel Discussion (Misc queries) | |||
How do I combine a list of dates & a value into 1 week intervals | Excel Discussion (Misc queries) | |||
Counting dates for a the present month but not future months | Excel Worksheet Functions |