Simon
Simon

Reputation: 3

Only add a value in the cell if there is a date present

I'm new here so excuse the probably unelegant excel formula. Please feel free to show me the proper way of doing this.

I am tracking the number of emails I send by date. After 7 emails are sent I just want to leave the cell as "7 emails sent"

I've tried adding an extra IF statement for beyond 12 days but that will also include cells that have no entry at all. Tried playing with the OR but can't figure out how it works with these IFs.

=IF(F4=TODAY(),"1 Email Sent",IF(F4=TODAY()-2,"2 Emails Sent",IF(F4=TODAY()-3,"2 Emails Sent",IF(F4=TODAY()-4,"3 Emails Sent",IF(F4=TODAY()-5,"3 Emails Sent",IF(F4=TODAY()-6,"4 Emails Sent",IF(F4=TODAY()-7,"4 Emails Sent",IF(F4=TODAY()-8,"4 Emails Sent",IF(F4=TODAY()-9,"5 Emails Sent",IF(F4=TODAY()-10,"6 Emails Sent",IF(F4=TODAY()-11,"6 Emails Sent",IF(F4=TODAY()-12,"All 7 Emails Sent",IF(F4<TODAY()-13,"")))))))))))))

I'd just like to be able to say IF(F4<TODAY()-12,"All 7 Emails Sent" but where there is no date in F4 - just leave the cell empty.

Upvotes: 0

Views: 248

Answers (2)

Simon
Simon

Reputation: 3

Thank you Jenn. That revision works perfectly.

=IF(OR(NOT(ISNUMBER(A1)),A1>TODAY()),"",VLOOKUP(EDATE(TODAY(),0)-EDATE(A1,0),$E$2:$F$16,2,1))

You're a star.

Upvotes: 0

Jenn
Jenn

Reputation: 647

This formula will return "All 7 Emails Sent" if A1 either contains a date that is more than 12 days in the past or if A1 is blank.

IF(OR(ISBLANK(A1),A1<TODAY()-12),"All 7 Emails Sent")

enter image description here

Revision -

There are actually several better ways to handle this, but are based on what it is that you are trying to accomplish. Create a table on separate worksheet in the workbook that matches the table on the right in the screenshot below. This will be your lookup table. In the example illustrated, Excel counts the number of days between the date in the adjacent cell and today. Then looks for that number in the first column of the lookup table. If found, the adjacent cell on the left is returned. It is also based on an approximate match vs exact match, which means it will return the closest match as well (for your records that are more than 7 messages). Also, before Excel runs the calculation, if first makes sure the cell to the left is number, otherwise it returns blank. Is this what you are looking for?

IF(OR(NOT(ISNUMBER(A1)),A1>TODAY()),"",VLOOKUP(EDATE(TODAY(),0)-EDATE(A1,0),$E$2:$F$16,2,1))

enter image description here

Upvotes: 1

Related Questions