Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
I have a spreadsheet that has a number of items in it. Each with their own
due date. I would like to have another summary worksheet that shows me the number of items that are due within the next 15 days, 30 days, 45 days and 60 days. I don't need to know the specific items just the count of the items due in these periods. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Seems like simple algebra. I think more information is required to do this
right; otherwise just taking shots in the dark. In the meantime, consider this...this would be a nice touch once you are done with the calculations: http://www.contextures.com/xlCondFormat01.html It will allow you to apply colors (formatting) to the cells that meet certain conditions (which you define). Regards, Ryan-- -- RyGuy "fluffy" wrote: I have a spreadsheet that has a number of items in it. Each with their own due date. I would like to have another summary worksheet that shows me the number of items that are due within the next 15 days, 30 days, 45 days and 60 days. I don't need to know the specific items just the count of the items due in these periods. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Try somethong like:
=COUNTIF(Sheet1!A1:A25,"<"&TODAY()-15) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "fluffy" wrote in message ... I have a spreadsheet that has a number of items in it. Each with their own due date. I would like to have another summary worksheet that shows me the number of items that are due within the next 15 days, 30 days, 45 days and 60 days. I don't need to know the specific items just the count of the items due in these periods. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
Let me clarify further. I have a spreadsheet that has a worksheet with over
100 items that sort into three different categories, each with it's own target date. I would like to have another worksheet that would show me the number of items in category A, B, and C that have a target dates of 15 days or less from todays date. I would further like to capture the cound of those due between 15 days and 30 days from today. 31 and 40, 41 and 60. I hope this helps a bit. "Sandy Mann" wrote: That would count every date before 15 days ago I think that you may mean to count dates in the lastr 15 days. If so try: =SUMPRODUCT((Sheet1!A1:A25TODAY()-16)*(Sheet1!A1:A25<TODAY())) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try somethong like: =COUNTIF(Sheet1!A1:A25,"<"&TODAY()-15) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "fluffy" wrote in message ... I have a spreadsheet that has a number of items in it. Each with their own due date. I would like to have another summary worksheet that shows me the number of items that are due within the next 15 days, 30 days, 45 days and 60 days. I don't need to know the specific items just the count of the items due in these periods. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel
It would if I was thinking straight! <g
With the Dates in A2:A150 Categories in B2:B150 (ie Cat A, Cat B or whatever the real names are) =SUMPRODUCT((Sheet1!A2:A150TODAY())*(Sheet1!A2:A1 50<TODAY()+16)*(Sheet1!B2:B150="Cat A")) will return a count of all dates that are later than today but not as far in advance as 16 days after today and the Category in Column B is "Cat A" =SUMPRODUCT((Sheet1!A2:A150TODAY()+14)*(Sheet1!A2 :A150<TODAY()+31)*(Sheet1!B2:B150="Cat A")) will do the same thing for dates 15 days or more from today up to 30 days from today. 31 to 40 use +30 and +41 and 41 to 60 use +40 and +61 respectively. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "fluffy" wrote in message ... Let me clarify further. I have a spreadsheet that has a worksheet with over 100 items that sort into three different categories, each with it's own target date. I would like to have another worksheet that would show me the number of items in category A, B, and C that have a target dates of 15 days or less from todays date. I would further like to capture the cound of those due between 15 days and 30 days from today. 31 and 40, 41 and 60. I hope this helps a bit. "Sandy Mann" wrote: That would count every date before 15 days ago I think that you may mean to count dates in the lastr 15 days. If so try: =SUMPRODUCT((Sheet1!A1:A25TODAY()-16)*(Sheet1!A1:A25<TODAY())) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Try somethong like: =COUNTIF(Sheet1!A1:A25,"<"&TODAY()-15) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "fluffy" wrote in message ... I have a spreadsheet that has a number of items in it. Each with their own due date. I would like to have another summary worksheet that shows me the number of items that are due within the next 15 days, 30 days, 45 days and 60 days. I don't need to know the specific items just the count of the items due in these periods. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|