+ Reply to Thread
Results 1 to 4 of 4

IF formula to calculate values between a range of dates

  1. #1
    Registered User
    Join Date
    09-14-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    Professional Plus 2010 Ver 14.0.7172.5000 (32-bit)
    Posts
    21

    IF formula to calculate values between a range of dates

    Hi,
    I have a table that has 2 columns (Date range and Percent). The date format is MM/DD/YY.

    I like to write an IF formula that takes Employee Hire Dates to calculate a value using their Base Salary and multiply by the Percent in the 2nd column. I like to do this for about 500 employees where I can use one same formula to calculate for all employees. For example, if an employee is hired on 6/1/19 and has a Base Salary of $100,000, the formula will calculate: 100,000 x 25%.

    Date Percent
    1/1/21 - 12/31/21 0%
    1/1/19 - 12/31/20 25%
    1/1/17 - 12/31/18 50%
    1/1/15 - 12/31/16 75%
    < 12/31/14 100%

    I know I can write something inside the IF formula that looks something like this: IF(AND(A2>=DATEVALUE(“1/1/19”),A2<=DATEVALUE(“12/31/20″)), ..................
    but I am not sure how to write the entire full formula to capture all the 5 conditions in the table.

    I would appreciate any help you can provide.
    Thank you

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,915

    Re: IF formula to calculate values between a range of dates

    Try Below Formula

    =IF(A2<=0,"",IF(A2<=DATE(2014,12,31),100%,IF(A2<=DATE(2016,12,31),75%,IF(A2<=DATE(2018,12,31),50%,IF(A2<=DATE(2020,12,31),25%,0)))))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    09-14-2016
    Location
    Minneapolis, MN
    MS-Off Ver
    Professional Plus 2010 Ver 14.0.7172.5000 (32-bit)
    Posts
    21

    Re: IF formula to calculate values between a range of dates

    Hi Samba,
    Unfortunately, it doesn't work as it is only giving a result of either 0 or 100.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,427

    Re: IF formula to calculate values between a range of dates

    No it doesn't.

    Post a sample sheet if you can't get it going.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-25-2018, 11:28 PM
  2. [SOLVED] Formula to calculate sum/average based on number of unique dates in range
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-19-2014, 06:47 PM
  3. [SOLVED] MAX IF formula to find various highest values between a range of dates
    By Smudge.Smith in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-23-2013, 04:04 AM
  4. Replies: 1
    Last Post: 11-01-2012, 03:41 PM
  5. Formula to calculate highest values in a range
    By Dabooj in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-09-2008, 10:42 AM
  6. [SOLVED] Formula to retrieve range of dates from a worksheet to calculate d
    By accented in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-10-2006, 05:55 PM
  7. Formula to calculate a total in one range based on dates from another column
    By CarolineD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-01-2005, 04:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1