+ Reply to Thread
Results 1 to 2 of 2

Need to add Date value subraction to IF statement

  1. #1
    Registered User
    Join Date
    11-10-2016
    Location
    Cleveland, OH
    MS-Off Ver
    2010
    Posts
    16

    Need to add Date value subraction to IF statement

    This formula is to multiply days worked times a variable to establish paid time off earned.

    AF30 cell is the arbitrary entered date, often today
    AQ1 is hire date
    V31 daily accrual rate if less than 5 years with the company
    V32 daily accrual rate if more than 5 years but less than 10
    V33 daily accrual rate if more than 10 years

    problem I've found is if the employee has been with the company less than a year, it needs to calculate the amount of days worked times the daily accrual rate.
    i.e. 8/15/17 worked till today (11/22/17) should yield 99 days times the value in V31 yielding 21.69783 hours not 71 hours, as if the employee worked all year.

    =IF(ROUND((AF30-DATEVALUE("1/1/2017"))*IF(AF30>DATE(YEAR(AQ1)+10,MONTH(AQ1),DAY(AQ1)),V33,IF(AF30>DATE(YEAR(AQ1)+5,MONTH(AQ1),DAY(AQ1)),V32,V31)),0)<0,0,ROUND((AF30-DATEVALUE("1/1/2017"))*IF(AF30>DATE(YEAR(AQ1)+10,MONTH(AQ1),DAY(AQ1)),V33,IF(AF30>DATE(YEAR(AQ1)+5,MONTH(AQ1),DAY(AQ1)),V32,V31)),0))

    tired to add =DATEDIF(AQ1,AF30,"d") to the final equation; but yeah .... it didn't work.

    I'm stuck on this doozy.

    Help please

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,548

    Re: Need to add Date value subraction to IF statement

    Since I made the sample file I put things where they were easy to reference. However, I imagine that you will not have much trouble in changing the cell references to go along with your set up.
    The accrual formula is:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] If statement with a date
    By lorber123 in forum Excel General
    Replies: 2
    Last Post: 04-28-2016, 01:56 PM
  2. Replies: 1
    Last Post: 08-13-2015, 03:19 PM
  3. Replies: 2
    Last Post: 03-13-2015, 12:03 PM
  4. [SOLVED] If Statement with today's date minus due date for a delay calculation
    By RDFUC in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-26-2013, 02:58 PM
  5. [SOLVED] If statement to check if date falls between date range
    By mcranda in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-21-2013, 07:31 PM
  6. IF Statement to return value if a date is before another date
    By david1987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-18-2012, 04:59 AM
  7. Replies: 8
    Last Post: 12-21-2007, 12:21 AM

Tags for this Thread

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