excel & dates using If

ikthius · 25-10-2007, 19:27

hey everyone....

trying to get a small thing working.

what I am trying is to have an if statement working.

I have a column of start dates and a column of end dates.

I want to have a week 1 show if todays date falls in between these dates.

ok it works as a one off, but in a full formula it does not.

A1: 22 oct 07 A2: =sum(A1+7) etc
B1: 28 oct 07 B2: =sum(B1+7) etc
C1: =today() will always stay the same cell

formula:
=if($C$1>=A1&$C$1<=B1,"Week 1","")

ok say I got 10 rows I should only expect one row to show what week it is, each formula will go up in weeks.

why this not work?

ik

TheNorm · 25-10-2007, 19:39

Quote:

Originally Posted by ikthius

...=if($C$1>=A1&$C$1<=B1,"Week 1","")...

What is the purpose of "A1&$C$1"?

cimt · 25-10-2007, 19:42

Quote:

Originally Posted by TheNorm

What is the purpose of "A1&$C$1"?

I think that is mean to be in between the start date and todays date?

TheNorm · 25-10-2007, 19:44

Quote:

Originally Posted by cimt

I think that is mean to be in between the start date and todays date?

Oh! You want to combine two IF statements? How about this:

Code:

=IF($C$1>=A1,IF($C$1<=B1,"Week 1",""),"")

ikthius · 25-10-2007, 19:47

Quote:

Originally Posted by TheNorm

What is the purpose of "A1&$C$1"?

did I not try and explain it???

ok column A has a row of start dates like monday one week
column B has a row of end dates say sunday

but C1 is today as in the formula =TODAY() so this updates all the time.
so I made this the same cell to be referenced all the time e.g. $C$1

I want to see if cell C1 is between A1 and B1.

ik

thats it, I was trying to combine it with an & operand

Tricky · 25-10-2007, 22:56

=if(and(a1>=today(),b1<=today()),true,false)

this what you mean?!