Need assistance with IF function when calculating dates
 

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Let's say
Move out date in A2: 11/30/2006
Move in date in B2: 12/14/2006

Formula in C2 =IF(ISNUMBER(B2),"occupied","vacant")


"MMangen" wrote:

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Okay, assuming Today is in A1, Move out is in column A starting at A2 and
Move In is in column B starting in B2, In Column C:

=IF(AND(A2<$A$1,B2$A$1),"Vacant","Occupied")

"MMangen" wrote:

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Just thought of something... B2 could be blank if the move in date is not
known...

So...

=IF=IF(AND(A2<$A$1,OR(B2$A$1,B2="")),"Vacant","Oc cupied")

"~L" wrote:

Okay, assuming Today is in A1, Move out is in column A starting at A2 and
Move In is in column B starting in B2, In Column C:

=IF(AND(A2<$A$1,B2$A$1),"Vacant","Occupied")

"MMangen" wrote:

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Without the first =IF

"~L" wrote:

Just thought of something... B2 could be blank if the move in date is not
known...

So...

=IF=IF(AND(A2<$A$1,OR(B2$A$1,B2="")),"Vacant","Oc cupied")

"~L" wrote:

Okay, assuming Today is in A1, Move out is in column A starting at A2 and
Move In is in column B starting in B2, In Column C:

=IF(AND(A2<$A$1,B2$A$1),"Vacant","Occupied")

"MMangen" wrote:

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Almost but not quite.....somethings not quite right with part of the formula
yet.

Here is what I have:

Cell A1 is today's date
Cell A10 is the move out date: 11/30/06
Cell C10 is the new move in date - which in this case is indeed 12/14/06,
however as "L" mentioned it could indeed be blank because the new date is not
yet known.

Cell B10 needs to have "vacant" entered if the move out date is earlier than
today and cell C10 is blank.

Cell B10 needs to have "occupied" entered if the move out date has not yet
happened (e.g. move out date was 12/31/06) or if C10 had a date earlier than
today - 12/14/06 with the above example.

I haven't tried "teethless mama's" suggestion, but I'm getting ready to.

On "L's" answer I think it's just a minor tweak that is needed for it to
work as hoped!

"~L" wrote:

Without the first =IF

"~L" wrote:

Just thought of something... B2 could be blank if the move in date is not
known...

So...

=IF=IF(AND(A2<$A$1,OR(B2$A$1,B2="")),"Vacant","Oc cupied")

"~L" wrote:

Okay, assuming Today is in A1, Move out is in column A starting at A2 and
Move In is in column B starting in B2, In Column C:

=IF(AND(A2<$A$1,B2$A$1),"Vacant","Occupied")

"MMangen" wrote:

I have a spreadsheet that I have do the following (that is currently working)

There is a move out date in one column and when we open the sheet it has an
IF function that will tell us if an apartment is vacant or occupied based on
the move out date and today's date. That part is working fine.

However, we have another column in which we can indicate the move in date of
a new person. I can't get the vacant and occupied column to update correctly
based on this date.

Hopefully what I am trying to do works. Here is a scenario that may explain
it better.

Unit A moved out on 11/30/06 so the current formula knows that today is
12/19/06 and therefore knows that based on the current IF statement that the
unit is vacant (and returns "vacant" in the appropriate cell). However, next
step is that the apartment was re-rented as of 12/14/06 so actually it is
occupied.

Need assistance with IF function when calculating dates
 

Hi MMangen,

Can you provide values for A and C where the expected result is not produced
with the formula?

I tested the following:
(Today - 12/20)
Move Out: 11/30/06, Move In: 12/14/06, Result: Occupied

"Cell B10 needs to have "vacant" entered if the move out date is earlier
than today and cell C10 is blank."

TEST 1: Move Out: 12/15/06, Move In: <Blank, Result: Vacant

"Cell B10 needs to have "occupied" entered if the move out date has not yet,
happened (e.g. move out date was 12/31/06) or if C10 had a date earlier than
today - 12/14/06 with the above example."

TEST 2: Move Out: 12/31/06, Move In: <Blank, Result: Occupied
TEST 3: Move Out: 12/9/06, Move In: 12/14/06, Result: Occupied

Do all of these match your expectations?

There are 9 scenarios as I see it... here are my expectations for each,
which the formula has met so far.
For move out / move in: Expected result
Blank/Blank: Vacant
Past/Blank: Vacant
Future/Blank: Occupied
Blank/Past: Occupied
Past/Past: Occupied
Future/Past: Occupied
Blank/Futu Vacant
Past/Futu Vacant
Future/Futu Occupied



"MMangen" wrote:

Almost but not quite.....somethings not quite right with part of the formula
yet.

Here is what I have:

Cell A1 is today's date
Cell A10 is the move out date: 11/30/06
Cell C10 is the new move in date - which in this case is indeed 12/14/06,
however as "L" mentioned it could indeed be blank because the new date is not
yet known.

Cell B10 needs to have "vacant" entered if the move out date is earlier than
today and cell C10 is blank.

Cell B10 needs to have "occupied" entered if the move out date has not yet
happened (e.g. move out date was 12/31/06) or if C10 had a date earlier than
today - 12/14/06 with the above example.

I haven't tried "teethless mama's" suggestion, but I'm getting ready to.

On "L's" answer I think it's just a minor tweak that is needed for it to
work as hoped!