| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
count between a date range
Hi, I want to be able to count between a date range and return the value yes.
I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Try..
=IF(M2=datecell-DATE(1,0,0),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Hi
This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Probably lost here...Can you post back with samples and the expected results...
-- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I
would like to mark the ones which are one year before the date shown in a seperate sheet. So if the date shown is 01/02/2010, I would like the formula to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. Nothing before or after. Does that make sense? Thanks "Jacob Skaria" wrote: Probably lost here...Can you post back with samples and the expected results... -- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Try
=IF(AND(M2<=DateCell,M2=DateCell-365),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I would like to mark the ones which are one year before the date shown in a seperate sheet. So if the date shown is 01/02/2010, I would like the formula to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. Nothing before or after. Does that make sense? Thanks "Jacob Skaria" wrote: Probably lost here...Can you post back with samples and the expected results... -- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Thats great! Thank you! I do have one more question. Should I open another
post? Thanks David "Jacob Skaria" wrote: Try =IF(AND(M2<=DateCell,M2=DateCell-365),"Yes","No") -- Jacob (MVP - Excel) "Dave" wrote: OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I would like to mark the ones which are one year before the date shown in a seperate sheet. So if the date shown is 01/02/2010, I would like the formula to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. Nothing before or after. Does that make sense? Thanks "Jacob Skaria" wrote: Probably lost here...Can you post back with samples and the expected results... -- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
Hi Dave
Try =IF(AND(M2<=datecell, M2=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No") -- Regards Roger Govier "Dave" wrote in message ... OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I would like to mark the ones which are one year before the date shown in a seperate sheet. So if the date shown is 01/02/2010, I would like the formula to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. Nothing before or after. Does that make sense? Thanks "Jacob Skaria" wrote: Probably lost here...Can you post back with samples and the expected results... -- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
count between a date range
This works just as well! Thanks alot!
"Roger Govier" wrote: Hi Dave Try =IF(AND(M2<=datecell, M2=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No") -- Regards Roger Govier "Dave" wrote in message ... OK. I have a number of dates in column a from 01/11/2007 to 01/06/2010. I would like to mark the ones which are one year before the date shown in a seperate sheet. So if the date shown is 01/02/2010, I would like the formula to mark every row with a date inbetween 01/02/2009 and the 01/02/2010. Nothing before or after. Does that make sense? Thanks "Jacob Skaria" wrote: Probably lost here...Can you post back with samples and the expected results... -- Jacob (MVP - Excel) "Dave" wrote: Hi This does not work. It counts a year back plus one month and gives a yes to dates beyond the date in M2. So it doesnt work. I have tried to manipulate it but to no avail!? Thanks "Dave" wrote: Hi, I want to be able to count between a date range and return the value yes. I will show you the formula I currently have: =IF(M2=DateCell-365,"Yes","No") This gives a yes if the date is greater than the date in M2 minus 365. This however cincludes all the dates beyond M2 which isnt what I want. I want the dates upto M2. If that makes sense? Any help would be greatly appreciated Thanks __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com __________ Information from ESET Smart Security, version of virus signature database 5165 (20100602) __________ The message was checked by ESET Smart Security. http://www.eset.com . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count a value in a date range based on a date in another workshee. | Excel Worksheet Functions | |||
| Count with date range | Excel Worksheet Functions | |||
| Count within a date range | Excel Worksheet Functions | |||
| How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
Hybrid Mode
