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 |
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 |