Is there a between function

[Guest]

I Have set up an if function to calculate the amount of day's between two dates. I want to conditional format a cell to show a different message if the amount of day's passed fall between say 30 and 0. Below is what I am trying to do.

A1 contains today's date. A2 contains the date something was last done. I would like the if statement to work out if the difference between A2 and A1 is between 150 day's and 180 day's the resultant conditional format would read 30 day's to go. If The result was greater than 180 day's the result would be Overdue. If the result was between 0 and 150 then the result would read Okay.

I can do most of it myself except I do not know how to get a formula for between or how to join all the <=-+> together

Bobby

 

[Arvi Laanemets]

Hi

1. No need for value in A1 there

=IF(A2="","",IF(A2-TODAY()<0,"Overdue",IF(A2-TODAY()>30,"OK",(A2-TODAY()) &
" day"&IF(A2-TODAY()<>1,"s","") & " to go")))


--
(When sending e-mail, use address (e-mail address removed))
Arvi Laanemets

I Have set up an if function to calculate the amount of day's between two

dates. I want to conditional format a cell to show a different message if
the amount of day's passed fall between say 30 and 0. Below is what I am
trying to do.

A1 contains today's date. A2 contains the date something was last done. I

would like the if statement to work out if the difference between A2 and A1
is between 150 day's and 180 day's the resultant conditional format would
read 30 day's to go. If The result was greater than 180 day's the result
would be Overdue. If the result was between 0 and 150 then the result would
read Okay.

 

[J.E. McGimpsey]

Are you asking for a function to return a value (e.g., "Okay",
"Overdue"), or are you asking for a conditional format?

Value:

=IF(A2-A1>180,"Overdue",IF(A2-A1>150, 180 - (A2-A1) & " days to
go.", "Okay")

Conditional Format:

CF1: Formula Is =(A2-A1)>180 ==> Format for Overdue
CF2: Formula Is =(A2-A1)>150 ==> Format for Due
Regular formatting ===> Okay.

 

[JMay]

In C1 enter the following Nested-If Statement:
=IF(A1-B1<=150,"OK",IF(AND(A1-B1>150,A1-B1<180),"30-Days to go","OverDue"))
and copy down.
Separately use the 3 CF conditions using CellValue is OK Green, etc
HTH

I Have set up an if function to calculate the amount of day's between two

dates. I want to conditional format a cell to show a different message if
the amount of day's passed fall between say 30 and 0. Below is what I am
trying to do.

A1 contains today's date. A2 contains the date something was last done. I

would like the if statement to work out if the difference between A2 and A1
is between 150 day's and 180 day's the resultant conditional format would
read 30 day's to go. If The result was greater than 180 day's the result
would be Overdue. If the result was between 0 and 150 then the result would
read Okay.

 

[Freemini]

one way to do this instead of trying to use conditional formatting is to
put the following formula into A3
=IF(OR(A1-A2=0,A1-A2<=150),"Okay",IF(AND(A1-A2>150,A1-A2<180),180-(A1-A2)&"
Days to Go","Overdue"))

Hope this is what you are looking for

Mike

 

[Guest]

Thank you all Jmay formula was the easiest for me to understand and the closest to what I had done so far

Bobby