Viewing 10 posts - 1 through 10 (of 10 total)
  • Dummy Excel question
  • BoardinBob
    Full Member

    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!

    Stoner
    Free Member

    In C2:

    =IF(AND(C$1>=EOMONTH($A2, -1), C$1<EOMONTH($B2, 0)), 1, 0)

    BoardinBob
    Full Member

    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

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    Stoner
    Free Member

    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.

    BoardinBob
    Full Member

    Bloody work computer want let me add in the analysis toolpak 👿

    Stoner
    Free Member

    hang on then, Ill do it the old way 🙂

    Stoner
    Free Member

    =IF(AND($A2<=(D$1-1), $B2>(C$1-1)), 1, 0)

    stumpyjon
    Full Member

    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)

    Stoner
    Free Member

    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

    stumpyjon
    Full Member

    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.

Viewing 10 posts - 1 through 10 (of 10 total)

The topic ‘Dummy Excel question’ is closed to new replies.