MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
In column A I have a list of start dates
In column B I have a list of end dates
Column C onwards will be headed as sequential months & years i.e. Column C is headed Jan-06, Column D is Feb-06, Column E is Mar-06
Each row represents a person
In column C onwards I want to look at the header for each column and see if it lies between the data in column A & B. If it does I want to enter a 1, if it doesn’t I want to enter a zero
Example
=IF(AND(C1>=A2,C1<=B2),1,0)
So for example column C is for Jan-06. The formula then looks in A2 and says was the start date greater than or equal to Jan-06 and then looks in B2 and says was the end date less than or equal to Jan-06. If that’s the case a 1 appears, if not a zero.
Now the problem I have is the Jan-06, Feb-06, Mar-06 headers are being stored as 01/01/2006, 01/02/06, 01/03/06
Then, if the start date in column A is higher than that i.e. 02/01/2006, the formula returns a value of 0 but I want it to enter a 1.
Is there a way I can only look at the month and years in columns A&B and compare that to the month at the top of columns C, D, E etc
I’m sure there’s a basic solution but I’m going round in circles!
In C2:
=IF(AND(C$1>=EOMONTH($A2, -1), C$1<EOMONTH($B2, 0)), 1, 0)
In C2:=IF(AND(C$1>=EOMONTH($A2, -1), C$1<EOMONTH($B2, 0)), 1, 0)
Throws up an error when I try that.
A2 = 01/01/2005
B2 = 07/01/2006
C1 = 01/01/2006
And when i stick that formula in C2 I get a "#NAME?" error
go to tools>add-ins
select Analysis toolpak and turn it on.
You need to have the additional functionality on to use some of the more useful date functions.
Bloody work computer want let me add in the analysis toolpak 👿
hang on then, Ill do it the old way 🙂
=IF(AND($A2<=(D$1-1), $B2>(C$1-1)), 1, 0)
Didn't know you could have used the And like that, I'd have done:
=if(A2<=(D$1-1,if(B2>C$1-1,1,0),0)
nested IF statements arent particularly efficient, and in fact you are limited to no more than 7 nested IFs anyway.
If you need more then 7 then you're probably better off using VBA or something like a CHOOSE
Hee, just used that new form for the first time, should make nested IF's a thing of the past.
As for VBA.......just had a 2 hour meeting with the IT developements guys this morning discussing the merits of me writing stuff in Excel rather than using Delphi to create applications from scratch, still don't know where I stand.