Two criteria to check if a date in Excel

[Solved/Closed]
Report
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
-
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
-
Despite my best efforts going round in circles on a simple problem. Help gratefully accepted....
If say, Cell A1 can have two values - "In" or "Out" and cell B1 can have a date or have no date in it;
1. I want to say if you find "In" or "Out" in cell A1 and cell B1 has a date in it, return a null.
2. I want to say if you find "In" or "Out" in cell A1 and cell B1 has no date in it, return "No Date".

My best attempt yet was
=IF((AND(J912=OR("In""Out"),ISBLANK(I912))),"Y","N")

I know its simple for an experienced user but sending me daft - many thanks

7 replies

Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233
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.
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

Excellent
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

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
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233
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.
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

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.
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

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
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

I have tried =SUMPRODUCT((IF(ISBLANK(I3:I2948),"",(I3:I2948<=TODAY())*(J3:J2948="Out")))) but get a#value error
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233
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.
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015
>
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021

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.
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233 >
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

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.
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233 >
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021

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.
Posts
8
Registration date
Friday April 3, 2015
Status
Member
Last seen
April 4, 2015

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.
Posts
1313
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
August 3, 2021
233
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!