Numbers - Using dates in 'If' Statements

Question

Numbers - Using dates in 'If' Statements

Hi

Some help please with Numbers. I'm trying to set up an 'if' expression with two dates: an 'Expiry Date' and 'Today's Date'

There are three scenarios to report on:

a) if the 'Expiry Date' is more than 30 days before Todays Date then I want formula to report "Red"

b) if the 'Expiry Date' is between 30 days before Todays Date and Today, then report "Green"

c) If the 'Expiry Date' is after 'Todays Date' then report as "White"

The screenshot above shows the error I'm getting - basically it seems Im looking at 'duration i.e. days' and numbers, (apples and oranges)

Hope some-one can correct my error

thanks

Pierre

Posted on Apr 7, 2015 12:38 AM

Reply

Answer 1

Apr 8, 2015 5:49 PM in response to LuckyPierre3129

LP,

Duration Constants are difficult to use as literals in an expression, but it is quite easy if you place them in a table cell call them by cell reference.

Here's an example:

Another nice thing about this approach is that you can change the constants without going into the calculation expression. Just change the Constants table.

in my example, the expression for the code is:

=IF(B−A>Constants::B$1,"Red", IF(B−A>Constants::A$1, "Green", "White"))

Regards,

Jerry

Reply

Answer 2

Yellowbox

Level 7

21,945 points

Apr 7, 2015 4:43 AM in response to LuckyPierre3129

Hi Pierre,

Duration is your friend.

Formula in C2 (and Fill Down)

=TODAY

Formula in D2 (and Fill Down)

=C2−B2

Hide Column C

or use this formula

=TODAY()−B2

and you won't need Column C

Conditional Formatting.

Select all Body Cells in D.

Format Panel > Cell > Conditional Highlighting > Add a Rule

Enter 30d (30 days). Numbers changes that to 4w 2d (4 weeks 2 days).

Enter 0d. Numbers changes that to 0ms 😕.

Regards,

Ian.

Reply

Answer 3

Apr 8, 2015 6:00 PM in response to Jerrold Green1

Jerrold

You've nailed it for me - a very neat solution!

As you can see from post I was struggling with comparing the duration between two dates, i.e. 'days' with a 'number' - an Apples and Oranges problem - it can't been done. From the dim past when I used to use Excel, I had vaguely recalled that one could convert a date or time to a number, and thus be able to do an 'IF" statement that then compared a number to a number.

In 'Numbers' I couldn't find a function that converted the 'Date' (or 'Duration') to a number as in Excel; so this left me with my cry for help!!!

I can now continue with my work with my spreadsheet to generate the appropriate outcome based on the 'IF' statement

Many Thanks

Pierre

Reply