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

3
  • there is no image, edit the post and put the image or you can provide the image link. – iSR5 Apr 19 '16 at 22:28
  • I'm struggling! – Carla Apr 19 '16 at 22:38
  • Just upload the image to any site, and add the link in the post or comments. – iSR5 Apr 19 '16 at 22:39
2

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

12
  • 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 0:03
  • It is happening in the whole column. month/date/year xx/xx/xxxx – Carla Apr 20 '16 at 0: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 0: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 0:16

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