25-10-2007, 19:27
|
#1
|
|
Inactive
Join Date: Mar 2004
Location: Glasgow, Scotland
Services: anything for a new job
Posts: 4,165
|
excel & dates using If
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
|
|
|
|
25-10-2007, 19:39
|
#2
|
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: excel & dates using If
Quote:
Originally Posted by ikthius
...=if($C$1>=A1&$C$1<=B1,"Week 1","")...
|
What is the purpose of "A1&$C$1"?
|
|
|
|
|
25-10-2007, 19:42
|
#3
|
Join Date: Mar 2007
Location: Leeds
Age: 36
Posts: 5,255
|
Re: excel & dates using If
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?
|
|
|
|
|
25-10-2007, 19:44
|
#4
|
|
Inactive
Join Date: Jun 2006
Location: Cambridge
Services: Sky TV, VM TV, 20meg bb, tel, and a lobster (but the lobster died).
Posts: 4,349
|
Re: excel & dates using If
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",""),"")
|
|
|
|
|
25-10-2007, 19:47
|
#5
|
|
Inactive
Join Date: Mar 2004
Location: Glasgow, Scotland
Services: anything for a new job
Posts: 4,165
|
Re: excel & dates using If
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
|
|
|
|
|
25-10-2007, 22:56
|
#6
|
|
Inactive
Join Date: Jun 2003
Location: I am house...
Services: $KY+HD - BT Infinity
Posts: 2,284
|
Re: excel & dates using If
=if(and(a1>=today(),b1<=today()),true,false)
this what you mean?!
|
|
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
All times are GMT +1. The time now is 17:30.
|
Join CF
You are here: 
