Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
Hi guys,
I'm pretty new to Excel, but am trying to do something. I know about NETWORKDAYS. The query I have is different, I think. I have a cell holding Start Date, and a cell holding End Date. On another sheet, I have a column holding specific dates. For example, public holidays. Is there a way to get the number of these public holidays that fall within my start/end date? For example: Start Date: 1st Jan, 2009 End Date: 10th Jan, 2009 Exception Dates: 4th Jan 8th Jan 12th Jan Based on that data, I need to report '2' as the answer, as 4th and 8th fall inside my range. Is there a formula or something I can use to calculate this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
As long as *all* of your dates are true Excel dates. Try this:
A1 = start date B1 = end date Sheet2 A1:A3 = exception dates =COUNTIF(Sheet2!A1:A3,"="&A1)-COUNTIF(Sheet2!A1:A3,""&B1) -- Biff Microsoft Excel MVP "Cralis" wrote in message ... Hi guys, I'm pretty new to Excel, but am trying to do something. I know about NETWORKDAYS. The query I have is different, I think. I have a cell holding Start Date, and a cell holding End Date. On another sheet, I have a column holding specific dates. For example, public holidays. Is there a way to get the number of these public holidays that fall within my start/end date? For example: Start Date: 1st Jan, 2009 End Date: 10th Jan, 2009 Exception Dates: 4th Jan 8th Jan 12th Jan Based on that data, I need to report '2' as the answer, as 4th and 8th fall inside my range. Is there a formula or something I can use to calculate this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
Thanks Biff,
I'm not getting the correct result. This is what I have tried, based on your assistance. =COUNTIF( 'Public Holidays'!$B$4:$B$400,"="&B5)-COUNTIF( 'Public Holidays'!$B$4:$B$400,""&C5) The holidays are in a column on 'Public Holidays' spreadsheet. The cell (for this row) that has the start date is B5, with C5 holding the End Date. I have Zero display switched off, and with that, this is returning no value. Have I done something wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
I have Zero display switched off, and with that, this is returning no
value. Turn it back on (at least temporarily) and see if the formula returns 0. If it does then I suspect that your dates are not true Excel dates. The example dates you posted are not true Excel dates. Start Date: 1st Jan, 2009 End Date: 10th Jan, 2009 Exception Dates: 4th Jan 8th Jan 12th Jan -- Biff Microsoft Excel MVP "Cralis" wrote in message ... Thanks Biff, I'm not getting the correct result. This is what I have tried, based on your assistance. =COUNTIF( 'Public Holidays'!$B$4:$B$400,"="&B5)-COUNTIF( 'Public Holidays'!$B$4:$B$400,""&C5) The holidays are in a column on 'Public Holidays' spreadsheet. The cell (for this row) that has the start date is B5, with C5 holding the End Date. I have Zero display switched off, and with that, this is returning no value. Have I done something wrong? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
Biff!
My fault. It's infact working. It was showing some strange error, but when I copied the forumula to the rest of the rows, the error vanished. So, thanks very much! Works likem a charm. Now, just need to work out what it's doing! :) Thanks, Craig |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
Good deal. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Cralis" wrote in message ... Biff! My fault. It's infact working. It was showing some strange error, but when I copied the forumula to the rest of the rows, the error vanished. So, thanks very much! Works likem a charm. Now, just need to work out what it's doing! :) Thanks, Craig |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
I'm not 100% why I was getting the error. What happened was I created
the formula, which had a logic error. That is, it never displayed an error, but the output was unexpected. I copied that formula to all the rows below... When I edit a rows forumla with your correct version, it gave the error.. something about an inconsistant formula. I then copied that formula to all the rows again, and it was fine. Was it reporting that this row (that I had just edited) wasn't the same as all the other rows, and because I had copied them... Excel was just warning me that it wasn't the same as all the copied cells? (Hope that's understanable... :) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Number of dates falling in a date range
Yeah, that sounds like that was the "problem". Personally, I find all those
"warnings" to be annoying so I have them all turned off. -- Biff Microsoft Excel MVP "Cralis" wrote in message ... I'm not 100% why I was getting the error. What happened was I created the formula, which had a logic error. That is, it never displayed an error, but the output was unexpected. I copied that formula to all the rows below... When I edit a rows forumla with your correct version, it gave the error.. something about an inconsistant formula. I then copied that formula to all the rows again, and it was fine. Was it reporting that this row (that I had just edited) wasn't the same as all the other rows, and because I had copied them... Excel was just warning me that it wasn't the same as all the copied cells? (Hope that's understanable... :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
counting the number of dates in a date range | Excel Worksheet Functions | |||
Count dates falling in a certain range | Excel Worksheet Functions | |||
Falling within a range | Excel Discussion (Misc queries) | |||
How to assign a score, dependant on a sum falling within a range? | Excel Discussion (Misc queries) | |||
Skip holidays falling between two dates | Excel Worksheet Functions |