Two criteria to check if a date in Excel
Solved/Closed
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

Apr 3, 2015 at 07:36 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024  Apr 5, 2015 at 06:23 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024  Apr 5, 2015 at 06:23 AM
Related:
 Two criteria to check if a date in Excel
 Excel date format dd.mm.yyyy  Guide
 Check soft  Download  Finance
 How to check pc power consumption in windows 11  Guide
 How to check if someone is spying on my whatsapp  Guide
 Number to words in excel  Guide
7 responses
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Apr 3, 2015 at 09:49 AM
Apr 3, 2015 at 09:49 AM
Hello LearningCurve,
Perhaps this IF/And formula should do the trick for you:
=IF(AND(A1="",B1=""),"",IF(AND(A1="In",B1<>0),"",IF(AND(A1="Out",B1<>0),"",IF(AND(B1=""),"No Date",""))))
You will need to place the formula in cell C1.
Cheerio,
vcoolio.
Perhaps this IF/And formula should do the trick for you:
=IF(AND(A1="",B1=""),"",IF(AND(A1="In",B1<>0),"",IF(AND(A1="Out",B1<>0),"",IF(AND(B1=""),"No Date",""))))
You will need to place the formula in cell C1.
Cheerio,
vcoolio.
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 3, 2015 at 03:54 PM
Apr 3, 2015 at 03:54 PM
May I extend the question to a related bit for the formula below? Its when there is a choice of two different cell contents that catches me. I will sit and understand your kind reply which has worked very well.
=SUMPRODUCT(($I$4:$I$2948<TODAY())*($J$4:$J$2948="in"))
I need to search a range in a single column and if cells contain word In or Out total the number of dates in adjacent column that are =to or before todays date. The formula above works but onlly for cells tagged "In"
Many thanks
=SUMPRODUCT(($I$4:$I$2948<TODAY())*($J$4:$J$2948="in"))
I need to search a range in a single column and if cells contain word In or Out total the number of dates in adjacent column that are =to or before todays date. The formula above works but onlly for cells tagged "In"
Many thanks
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Apr 4, 2015 at 03:48 AM
Apr 4, 2015 at 03:48 AM
Hello LearningCurve,
You were almost there with your formula! Try this (just insert your own ranges):
=SUMPRODUCT((B:B<=TODAY())*(A:A="In")+(B:B<=TODAY())*(A:A="Out"))
You can check if the total is correct by using two formulae:
=SUMPRODUCT((B:B<=TODAY())*(A:A="In"))
=SUMPRODUCT((B:B<=TODAY())*(A:A="Out"))
and then summing the result (again, enter your own ranges)
You could also use two helper columns with these formulae:
=IF(B1>TODAY(),"0",IF(B1<=TODAY(),COUNTIF(A1:A1,"In")))
This is placed in C1 (assuming that your data is in columns A & B) and dragged down as far as needed.
=IF(B1>TODAY(),"0",IF(B1<=TODAY(),COUNTIF(A1:A1,"Out")))
This is placed in D1 and dragged down as far as needed.
These two formulae, when dragged down, will give you the count of both "In" and "Out" for each row of your range. You then use :
=SUMPRODUCT(C1:D26) (assuming that the data goes down to row 26 for example) to give the same result as the first formula above.
I hope that this helps you out.
Cheerio,
vcoolio.
You were almost there with your formula! Try this (just insert your own ranges):
=SUMPRODUCT((B:B<=TODAY())*(A:A="In")+(B:B<=TODAY())*(A:A="Out"))
You can check if the total is correct by using two formulae:
=SUMPRODUCT((B:B<=TODAY())*(A:A="In"))
=SUMPRODUCT((B:B<=TODAY())*(A:A="Out"))
and then summing the result (again, enter your own ranges)
You could also use two helper columns with these formulae:
=IF(B1>TODAY(),"0",IF(B1<=TODAY(),COUNTIF(A1:A1,"In")))
This is placed in C1 (assuming that your data is in columns A & B) and dragged down as far as needed.
=IF(B1>TODAY(),"0",IF(B1<=TODAY(),COUNTIF(A1:A1,"Out")))
This is placed in D1 and dragged down as far as needed.
These two formulae, when dragged down, will give you the count of both "In" and "Out" for each row of your range. You then use :
=SUMPRODUCT(C1:D26) (assuming that the data goes down to row 26 for example) to give the same result as the first formula above.
I hope that this helps you out.
Cheerio,
vcoolio.
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 4, 2015 at 04:37 AM
Apr 4, 2015 at 04:37 AM
Thank you for all your help. It has saved me so much time for something which is very important. I have been able to get on with the job while I will go through the formula when a little bit more quiet and make sure I understand how it was put together. Thanks again.
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 4, 2015 at 05:54 AM
Apr 4, 2015 at 05:54 AM
Sorry to be a nuisance but the last solution works fine but picks up null cells with no date in  sorry I didnt make it clear thatthereb were null cells
Didn't find the answer you are looking for?
Ask a question
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 4, 2015 at 06:16 AM
Apr 4, 2015 at 06:16 AM
I have tried =SUMPRODUCT((IF(ISBLANK(I3:I2948),"",(I3:I2948<=TODAY())*(J3:J2948="Out")))) but get a#value error
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Apr 4, 2015 at 08:31 AM
Apr 4, 2015 at 08:31 AM
Hello again,
Try this:
=IF(ISBLANK(A:B),"",SUMPRODUCT((B:B<=TODAY())*(A:A="In")+(B:B<=TODAY())*(A:A="Out")))
Again, enter your own ranges.
It also assumes that the "In" or "Out" values are not there beside the vacant date cells.
Cheerio,
vcoolio.
Try this:
=IF(ISBLANK(A:B),"",SUMPRODUCT((B:B<=TODAY())*(A:A="In")+(B:B<=TODAY())*(A:A="Out")))
Again, enter your own ranges.
It also assumes that the "In" or "Out" values are not there beside the vacant date cells.
Cheerio,
vcoolio.
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
>
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
Apr 4, 2015 at 09:51 AM
Apr 4, 2015 at 09:51 AM
Unfortunately there is either an "In" or "Out" against blank cells where the date would be. This all relates to boxes with documents stored in them (several thousand). I am trying to find out which boxes "In" storage have past their destruction date. I am also trying to find which boxes we have "Out" on site also have overdue destruction dates. I already identify how many boxes have no destruction dates. Is thsi workable in Excel. I would be very grateful as you have doen so much to help but this would complete my overview calculations. Thank you.
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
>
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 4, 2015 at 10:48 AM
Apr 4, 2015 at 10:48 AM
Hello again,
My preference in these type situations would be to use helper columns. If you want to try, drag this "In" formula down as far as needed (using your own ranges of course) in Column C (or whichever column you wish):
=IF(B1>TODAY(),"0",IF(B1="","",IF(B1<=TODAY(),COUNTIF(A1:A1,"In"))))
and drag this "Out" formula down as far as needed in Column D (or whichever column you wish):
=IF(B1>TODAY(),"0",IF(B1="","",IF(B1<=TODAY(),COUNTIF(A1:A1,"Out"))))
At the bottom of these two columns (or, again, wherever suits you on your sheet), enter these two formulae (above & below each other, side by side  whatever suits you) making sure that you reference the correct cell ranges:
=sum(C1:C50) (Example range only).
=sum(D1:D50) (Example range only).
Then below or beside these two formulae, enter this formula:
=sum(whatever:whatever) This just adds the two sum formulae above. This will give you your final count and also show "In" and "Out" separate counts.
This may be a better way for you, I hope!
Cheerio,
vcoolio.
My preference in these type situations would be to use helper columns. If you want to try, drag this "In" formula down as far as needed (using your own ranges of course) in Column C (or whichever column you wish):
=IF(B1>TODAY(),"0",IF(B1="","",IF(B1<=TODAY(),COUNTIF(A1:A1,"In"))))
and drag this "Out" formula down as far as needed in Column D (or whichever column you wish):
=IF(B1>TODAY(),"0",IF(B1="","",IF(B1<=TODAY(),COUNTIF(A1:A1,"Out"))))
At the bottom of these two columns (or, again, wherever suits you on your sheet), enter these two formulae (above & below each other, side by side  whatever suits you) making sure that you reference the correct cell ranges:
=sum(C1:C50) (Example range only).
=sum(D1:D50) (Example range only).
Then below or beside these two formulae, enter this formula:
=sum(whatever:whatever) This just adds the two sum formulae above. This will give you your final count and also show "In" and "Out" separate counts.
This may be a better way for you, I hope!
Cheerio,
vcoolio.
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
>
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
Apr 4, 2015 at 10:59 AM
Apr 4, 2015 at 10:59 AM
I just thought that you may like to have a look at my test work book here:
https://www.dropbox.com/s/6j0t651qm94893m/LearningCurve%28Sumproduct%29.xlsx?dl=0
You'll see some of the options at work
Cheerio,
vcoolio.
https://www.dropbox.com/s/6j0t651qm94893m/LearningCurve%28Sumproduct%29.xlsx?dl=0
You'll see some of the options at work
Cheerio,
vcoolio.
LearningCurve
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
Apr 4, 2015 at 02:41 PM
Apr 4, 2015 at 02:41 PM
Thank you so much for your patience, help and time. You have helped me produce far more superior statitsics and overview than I would have been able to do. It was kind to share your workbook. Once I have my sheet updated (there are a few pages not just one!) I will sit down and try and learn from your help. Again thank you so much.
vcoolio
Posts
1409
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 23, 2024
262
Apr 5, 2015 at 06:23 AM
Apr 5, 2015 at 06:23 AM
Hello again LearningCurve,
I was just perusing the test work book when it dawned on me and now I feel like such a dork! I became so absorbed with the SUMPRODUCT function that I forgot all about a simple COUNTIF function that would do the task for you:
=IF(OR(A:A="In",A:A="Out"),COUNTIF(B:B,"<="&TODAY()))
This formula will do as the original but will not count any blank cells, just as you would like.
I hope that this is a better result for you.
Cheerio,
vcoolio.
I was just perusing the test work book when it dawned on me and now I feel like such a dork! I became so absorbed with the SUMPRODUCT function that I forgot all about a simple COUNTIF function that would do the task for you:
=IF(OR(A:A="In",A:A="Out"),COUNTIF(B:B,"<="&TODAY()))
This formula will do as the original but will not count any blank cells, just as you would like.
I hope that this is a better result for you.
Cheerio,
vcoolio.
Apr 3, 2015 at 03:53 PM