Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
I feel I am close but I am not getting an accurate count. I have 100
activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
What you were probably trying to ask for was
=COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
Then you need to check your data. Are your data true excel dates, or text
masquerading as dates? Regards, Fred. "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
If it returns a value of zero then your data values are wrong, not the
formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
The dates switch back and forth when I reformat them. They appear as numbers
when I select general (under the format choices). I still get Zero as my solution but when I used a plus in front of the O5 and O6 I did at least get a value. It does not appear that there is any spaces in the cells "David Biddulph" wrote: If it returns a value of zero then your data values are wrong, not the formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
So when you see them as numbers when you format as General, are you sure
that you've got values which fall between the numbers you see in O5 and O6 (and is the number in O6 definitely larger than the number in O5)? Check again that you've got the data in the cells where you thought you had them, and that you haven't got the cell references wrong. If you are still struggling to find out what you've done wrong, break the formula down. =COUNTIF($H$8:$H$107,"<="&O6) gives the number of dates which are less than or equal to the end date in O6. =COUNTIF($H$8:$H$107,"<"&O5) gives the number of dates which are less than the starting date in O5 and which should therefore be omitted from the result. In each case, adjust the value in O5 or O6 and see whether it has the right effect on your formula result. The formula is simple, so it must be the data values which are wrong. -- David Biddulph "MR. NICE" wrote in message ... The dates switch back and forth when I reformat them. They appear as numbers when I select general (under the format choices). I still get Zero as my solution but when I used a plus in front of the O5 and O6 I did at least get a value. It does not appear that there is any spaces in the cells "David Biddulph" wrote: If it returns a value of zero then your data values are wrong, not the formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
How do I know if it is text disguised as text, if it is how do Change it to
true date format? "MR. NICE" wrote: The dates switch back and forth when I reformat them. They appear as numbers when I select general (under the format choices). I still get Zero as my solution but when I used a plus in front of the O5 and O6 I did at least get a value. It does not appear that there is any spaces in the cells "David Biddulph" wrote: If it returns a value of zero then your data values are wrong, not the formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
yes something is definitely wrong my dates are right and when I broke it down
it does not even want to count any of them "David Biddulph" wrote: So when you see them as numbers when you format as General, are you sure that you've got values which fall between the numbers you see in O5 and O6 (and is the number in O6 definitely larger than the number in O5)? Check again that you've got the data in the cells where you thought you had them, and that you haven't got the cell references wrong. If you are still struggling to find out what you've done wrong, break the formula down. =COUNTIF($H$8:$H$107,"<="&O6) gives the number of dates which are less than or equal to the end date in O6. =COUNTIF($H$8:$H$107,"<"&O5) gives the number of dates which are less than the starting date in O5 and which should therefore be omitted from the result. In each case, adjust the value in O5 or O6 and see whether it has the right effect on your formula result. The formula is simple, so it must be the data values which are wrong. -- David Biddulph "MR. NICE" wrote in message ... The dates switch back and forth when I reformat them. They appear as numbers when I select general (under the format choices). I still get Zero as my solution but when I used a plus in front of the O5 and O6 I did at least get a value. It does not appear that there is any spaces in the cells "David Biddulph" wrote: If it returns a value of zero then your data values are wrong, not the formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
counting cells that fall within a certain date range
I got it wow... it must of had something to do with text.... I extrapolate
this data out of another piece of soft ware and it configures it as a lostus 123 wks file and in the conversion it must do something so I had copy the data and paste it into an excel spreadsheet and it worked fine... I had just opened it and saved as a spreadsheet for some reasons this did not work... thanks for your help "MR. NICE" wrote: How do I know if it is text disguised as text, if it is how do Change it to true date format? "MR. NICE" wrote: The dates switch back and forth when I reformat them. They appear as numbers when I select general (under the format choices). I still get Zero as my solution but when I used a plus in front of the O5 and O6 I did at least get a value. It does not appear that there is any spaces in the cells "David Biddulph" wrote: If it returns a value of zero then your data values are wrong, not the formula. Check the values in the range $H$8:$H$107 and in O5 and O6. See what happens if you use Format/ Cells to reformat the cells. Will the values in the cells change to and fro between, for example, 17 Jun 2008 and 17/6/08 if you reformat the cells? If they don't, you've probably got text values rather than real dates. Check for extraneous spaces or other non-printing characters. -- David Biddulph "MR. NICE" wrote in message ... Yes I this is the formula I started out with and it returns the value of zero. There are actually 11 activities that fall within this date range "David Biddulph" wrote: What you were probably trying to ask for was =COUNTIF($H$8:$H$107,"<="&O6)-COUNTIF($H$8:$H$107,"<"&O5) -- David Biddulph "MR. NICE" <MR. wrote in message ... I feel I am close but I am not getting an accurate count. I have 100 activities and/or cells that have a date (the date represents when I need to be finished with the activity). I would like to get a count of how many activities each week I need to complete based on the dates that are in each cell. Currently I am using =COUNTIF($H$8:$H$107,"=<"+O6)+COUNTIF($H$8:$H$107, "="+O5) the O5 & O6 are the date range. the formula is counting but it is only counting the activities that fall ON the dates not within the range. What is up and can anyone help? If my boss only knew how much time I put into this freakn' formula..... Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Counting if numbers fall within a specified range | Excel Discussion (Misc queries) | |||
How to total itmes if they fall between a date range | Excel Worksheet Functions | |||
How to total items if they fall between a date range | Excel Discussion (Misc queries) |