Using IF with Dates in Numbers

Question

Level 1

4 points

Using IF with Dates in Numbers

I am trying to create a table that will tell me when I need to send a chaser email to a specific person. Start Date is when I first had contact with my client, Letter is when I need to send them a letter by (not really relevant to this problem), and the last three columns are individual people I need to chase for information (I’ve had to remove their names for obvious reasons).

I would like the cell in relation to the people that need to be chased to display “Chase” or “Wait” depending on whether or not the relevant date has arrived. The first person needs to be chased 4 days from my first contact with my client, the second needs to be chased 7 days from first contact with client, and the third person needs to be chased 10 days after contact with client.

Any help would be greatly appreciated.

Thank you!

Posted on Aug 27, 2024 7:05 AM

Reply

Answer 1

Camelot

Level 9

55,329 points

Aug 27, 2024 10:34 AM in response to LKerr00

The date calculations should be pretty easy, but it isn't clear where you expect the 'chase' and 'wait' labels to appear.

From your description it sounds like columns D, E, and F have names? are those different per-row? or is it the same name (i.e. in the header) and you just want the cells in those columns to flag 'Chase' or 'Wait'?

if the latter, then a simple IF() statement with DATEDIF() should suffice. For example. set D2 to:

=IF((DATEDIF(B2,NOW(),"D"))>=4,"Chase", "Wait")

This translates to:

First the difference in days between the cell B2 and the current date. If that's 4 or more then return "Chase", otherwise return "Wait")

You can fill that formula down the column to fill out the table.

You can then copy the formula into the adjacent columns and just change the calculation to >=7 and >=10 .

Two observations, though:

It isn't clear what to do on the '4 day chase' column on day 5+ - should this still say 'Chase' or do you assume that it's been chased? You can change '>= 4' to just '=4' if you only want it to flag Chase on day 4.

Secondly, you may want to use conditional highlighting to change the color of the cells to make it clearer which rows need follow up - people often react better to color highlighting, for example, than having to scan a whole table of Chase/Wait values to find the outliers. You might also just nix the 'wait' since it's redundant, which would also make the 'Chase' values stand out. Just a thought.

Reply

Answer 2

6x6

Level 6

15,686 points

Aug 27, 2024 10:06 AM in response to LKerr00

If “Start Date” is in column B and your contacts in columns D, E, and F, use this formula in D2:

IF(B2 + 4 >= TODAY(), “Chase”, “Wait”)

Similarly, the formula in E2 would be:

IF(B2 + 7 >= TODAY(), “Chase”, “Wait”)

Reply

Answer 3

LKerr00 Author

Level 1

4 points

Aug 27, 2024 11:26 AM in response to Camelot

I can’t thank you enough! You are entirely correct with what I was looking for (sorry I wasn’t clearer), and your solution works perfectly.

I’ve also implemented your suggestion regarding conditional highlighting, and I can already tell that this spreadsheet is going to massively help my workflow.

Thank you again!

Reply