Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF Formula with dates (98)

    Use the DATE function to to this: DATE(year,month,day) returns the date value for the given year, month and day.

    =IF(A1=DATE(1998,1,1),80,0)

  2. #2
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Formula with dates (98)

    Here's two suggestions:

    You can use this function to represent a date in your formula:
    DATE(1998,1,1)

    You could put the date in a cell and not in the formula. Do you have a column with the start date? Formulas can reference dates in cells without any problem and the same formula will work for every employee row. Typing each employee's start date into an individual formula sounds like a lot of work!
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF Formula with dates (98)

    <P ID="edit" class=small>Edited by JLC on 26-Jun-02 15:25.</P>Let me add to the statement below... we need the formula to include several date RANGES in it. Our database is downloaded from ADP so all employees hire dates are already in the spreadsheet... the formula would read from this column. Please see the attachment for an example. Thanks!

    We are using an Excel spreadsheet to calculate vacation hours based on years of employment. We would like to use the "IF" formula to determine how many vacation hours an employee would be granted. For example, if an employee started working here on January 01, 1998 we want to put 80 hours into the cell. The IF formula doesn't seem to like the date format inside the formula... it sees 01/01/98 as formula itself and divides 01 by 01 by 98. Does anyone have any suggestions? Thanks!
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Jun 2002
    Posts
    98
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: IF Formula with dates (98)

    Enter a table like this instead of the one you have now:
    <pre>
    Col A ColB
    12 Hired after Vac Hrs
    13 1/1/65 160
    14 1/1/88 120
    15 1/1/93 80
    16 1/1/01 40
    17 7/1/01 0
    </pre>


    Then use this formula to choose the number of vacation days. Cell I4 is the start date.

    =VLOOKUP(I4,A$13:B$17,2,TRUE)

    This means, lookup the start date in the table (A13:B17) and get the value in the second column. The "TRUE" parameter means, "If you can't find an exact match use the highest value without being greater than the start date." This is the same kind of lookup you'd use to figure out your tax rate on your income taxes. You lookup your income in a table that has cutoff points like this and pick the highest value without being greater than your income.

    Put the dollar signs in the formula to prevent that range from "slipping" when you copy the formula down the column for other employees.
    __________________________________________________ ____
    <img src=/S/nun.gif border=0 alt=nun width=20 height=20> Sister Dory
    Our Lady of Perpetual Help, Holstein Falls, Wisconsin, USA

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Formula with dates (98)

    JLC,

    I would use a nested IF statement, much like is found in the Excel help explaining grading schedules.

    Please see the attachment for an example of how to use it in your situation...I extended it out for a long range of dates to verify that it works like you wanted it to.

    HTH
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Formula with dates (98)

    JLC
    Please see the attachment .
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF Formula with dates (98)

    Thanks! Your formula worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •