How do I use the IF function to calculate date

  • Thread starter Pulling My Hair Out!
  • Start date
P

Pulling My Hair Out!

I am creating a statement form. I need it to take the date of each item on
the statement and calculate whether that item is less than 30 days, 31-60
days pastdue, 61-90 days past due, and finally over 90 days past due.
Depending on the out come of the value, I would like the amount of each item
placed into the appropriate box (current, 1-30 days past due, etc...)
Wow, upon reading the above, I think I've managed to confuse my self!
 
P

Peo Sjoblom

=IF(AND(TODAY()-A1<31,TODAY()>A1),"Less than or equal to
30",IF(AND(TODAY()-A1>30,TODAY()-A1<61),"Between 31 and 60
days",IF(AND(TODAY()-A1>60,TODAY()-A1<91),"Between 61 and 90
days",IF(TODAY()-A1>90,"more than 90 past","Not past"))))

or better

=IF(TODAY()>A1,VLOOKUP(TODAY()-A1,{0,"Less than or equal to 30";31,"Between
31 and 60";61,"Between 61 and 90";91,"Over 90"},2),"Not past due")

Regards,

Peo Sjoblom
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top