0

I have a very large spreadsheet with many dates in each row with the DD-MMM-YYYY format. I would like to return a 'YES' for all rows with dates less than a certain date (which will change) and return nothing if the value is greater than that date.

For ex. if I want to return all dates in a row less than 01-Mar-2018. I tried this formula:

=IF((J377:X377)

Any ideas as to what I am doing wrong? Thanks, Scott!

4
  • 1
    You are not using the correct syntax for IF. This should get you started on using IF correctly – cybernetic.nomad Oct 3 '18 at 15:29
  • 1
    I think you may be wanting a Filter instead? – BruceWayne Oct 3 '18 at 15:31
  • Make sure your Dates are actual numbers and not displayed numbers stored as text. You can test this out by using a formula like =isnumber(A1) or `istext(A1). Conversely, you can try changing the cell format to general. If it still looks like a date while formatted as general its text. If it changes to some large integer its a number. Note changing the format to date does not change how it is stored. Cell formatting only changes how information is stored. – Forward Ed Oct 3 '18 at 21:15
  • @Scott, your question is little confusing,, just make it clear that you have Dates in One Column like A1, A2 A3 or are Horizontally like A1, B1, C1 ? – Rajesh S Oct 4 '18 at 10:40
0

you need to use a formula for each date you want to check for example:

A1: 01-Mar-2018
B1: some other date you want to check against the first date (in A1)
C1: some other date you want to check against the first date (in A1)
D1: =IF(A1<B1,"YES","")
E1: =IF(A1<C1,"YES","")
0

Assuming all your dates are actually stored as integers and not strings, the following formula will verify each row for a date that is inclusive of a specified max/min range:

=IF(SUMPRODUCT(($B4:$D4>=$G$5)*($B4:$D4<=$G$4))>=1,"Yes","No")

Copy the formula down beside each row. Because SUMPRODUCT performs array like calculations, avoid full column/row reference such as A:A or 1:1 instead restrict it to the data you are looking at.

POC

0

My solution is based on both possible Formats.

  1. When Dates are only in one Column.
  2. When Dates are in more that one Columns.

enter image description here

Formula in Cell B3 when Dates are only in one Column.

=IF($C$2>A3,"Yes","")

Formula in Cell E14 when Dates are in many Columns.

=IF(COUNTIF(A14:C14,"<"&$C$2)=3,"Yes","")

Note:

  1. Parameter =3 is editable, depends on number of Date value in Columns.
  2. Cell C2 has Criteria 01-Mar-2018.
  3. Drag both the Formulas Down till needed.
  4. Adjust cell references in the Formula as needed.
2
  • Thank you very much! Sorry for the long delay but some health issues sidelined me. Your formula worked as I needed. I only modified it slightly because I have variable number of data in each row. The final formula looked like this - =IF(COUNTIF(I3:AC3,"<"&$AE$2)=(COUNT(I3:AC3)),"yes","no") – Scott Nov 1 '18 at 18:55
  • @Scott,, glad to hear from you and happy to learn that my solution has worked for you. Now, if you feel you can accept it as answer and Up vote also. Keep asking ☺ – Rajesh S Nov 2 '18 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.