2

enter image description here

I need help writing a formula for the following. superuser sample I hope you can see my image, and I explain myself well enough.

I want column L (my new column) to display column I(dollar amount) if Column C date is before Aug 31, 2015.

I want column M (my new column) to display the amount in Column I if the date is after Sept 1, 2015.

Column C has a name displayed as a title, column G has payment details listed. Then, the dates under each display name are listed in column C. charge codes are in column D, descriptions are in Column E, columns F,G,H are blank, and lastly, amounts are listed in column I.

Looking forward to a simple solution! Carla

Michael Frank
  • 7,855
  • 2
  • 39
  • 52
Carla
  • 17
  • 1
  • 1
  • 6

1 Answers1

3

You can use the DATE(year, month, day) function in an IF statement to compare dates.

For column L, you want to use something like:

=IF(C2<=DATE(2015,8,31),I2,"")

IF the value in C2 is less than (<=) or equal 31/08/2015 show the value in I2, else show nothing.

For column M, you can do the exact same thing but use greater than (>=) instead:

=IF(C2>=DATE(2015,9,1),I2,"")

enter image description here

Michael Frank
  • 7,855
  • 2
  • 39
  • 52
  • Thank you for your reply! You explained it so well, however, I must be doing something wrong. column L =IF(C10<=DATE(2015,8,31),I10,"") displays nothing in the column.Column M =IF(C10>=DATE(2015,9,1),I10,"") displays all amounts regardless of the date. :S – Carla Apr 19 '16 at 23:57
  • They both look correct, so it should be working. Is it only happening for `C10`? Also, how are your dates formatted in Column C? – Michael Frank Apr 20 '16 at 00:03
  • It is happening in the whole column. month/date/year xx/xx/xxxx – Carla Apr 20 '16 at 00:10
  • @Carla If you need to, take a screenshot with the Windows Snipping Tool and upload to imgur.com. Paste a link here and I will take a look at what's going on. – Michael Frank Apr 20 '16 at 00:10
  • @Carla It may be that Excel doesn't understand your date format. If you right click on one of the dates and choose `Format Cells...`, then go to `Date` and change the format, does the `Sample` change? If not, Excel doesn't like your dates and you may need to reload the dates with a different format `dd/mm/yyyy` for example. – Michael Frank Apr 20 '16 at 00:16
  • Hi, Michael! Thank you, again, for your reply and aid in my difficulty. I am going to start over to see if that helps clear it up. I will write again. – Carla Apr 20 '16 at 00:57
  • I have tried multiple times recreating my report, and retyping the formulas into new columns. I cannot outwit this! Any suggestions would be appreciated. – Carla Apr 20 '16 at 01:14
  • Ideally, I would like a sum from before 8.31.15 and a sum after 9.1.15. Could I achieve this with a different formula near the end of my document? – Carla Apr 20 '16 at 01:25
  • @Carla I have a feeling Excel doesn't recognise the dates in Column C as actual dates. Check [this question](http://superuser.com/questions/817110/unable-to-get-excel-to-recognise-date-in-column) for why this happens. – Michael Frank Apr 20 '16 at 01:25
  • Thank you, Michael! The date was definitely the issue. I was able to pull accurate formulas after implementing the 'text to column' advice. Thank you!!!! You made my night. :) – Carla Apr 20 '16 at 02:01
  • @Carla That's great to hear. Don't forget to mark the answer with a tick if it helped you out. Also, click the upvote arrow to mark it helpful! – Michael Frank Apr 20 '16 at 02:06
  • Hello again! I have been having issues with my formula. I have had some superuser help, but to no avail no resolution. I am writing to see if you wouldn't mind looking at it again? – Carla May 26 '16 at 17:21
  • Type the formula directly in the cell instead of the formula editor. The former will tell you the precise syntax it expects, to me it was DATE(yyyy;mm;dd), i.e. semicolon instead of comma as the separator. – bviktor Mar 24 '22 at 08:46