Previous Topic Next Topic

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 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Apr 5, 2015 at 06:23 AM
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
Related:

7 responses

Answer 1 / 7
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
Apr 3, 2015 at 03:53 PM
Excellent
0
Answer 2 / 7
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
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
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0
Answer 3 / 7
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
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.
0
Answer 4 / 7
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
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
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 7
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
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
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015 > vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024
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.
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 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
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.
0
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262 > vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024
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.
0
Answer 6 / 7
LearningCurve Posts 8 Registration date Friday April 3, 2015 Status Member Last seen April 4, 2015
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.
0
Answer 7 / 7
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 262
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.
0

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
You submitted an appeal. Check back here for results
asi -
cherryq -

2 responses