Results 1 to 8 of 8

Formula to change value on certain date

Threaded View

  1. #1
    Registered User
    Join Date
    06-10-2014
    Location
    Concord, NC
    MS-Off Ver
    2013
    Posts
    24

    Formula to change value on certain date

    I am trying to create a vacation tracker for work. I have the following fields:

    Hire Date (MM/DD/YYYY)
    Total Time with Company in years (=(TODAY()-<Hire Date Field>)/365)
    Vacation Hours Allotted
    Vacation Hours Remaining (Calculated from the Allotted Field and a sum of all of the time sheets within the workbook)

    I would like to have the Vacation Hours Alotted value dynamically reset to the appropriate value on the anniversary of the hire date posted, so employees can easily track "at a glance" how much time they have left before their vacation time resets. For example, if I was hired on February 1, I would like to be able to look at my vacation hours remaining field on January 1 and know how much time I have left so I can use it before I lose it.

    Is there a way to use an IF Statement to do this? I already have an IF Statement in there that sets the hours on specific anniversary dates, but not for every year:

    =IF(B2="","",INDEX({40,40,80,120,160}, MATCH(B2,{0,1,2,5,10})))

    I would rather not have to set a massive IF statement to get my desired result, and I am not 100% comfortable with using a macro or VBA to achieve the desired result.
    Attached Files Attached Files
    Last edited by TheTubaGeek; 08-14-2014 at 07:44 AM. Reason: Attached spreadsheeet

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Date change formula
    By ETCOLBY in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-01-2014, 08:03 AM
  2. how can i change the date date format in the following formula
    By kbgooner in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2014, 08:29 AM
  3. [SOLVED] Change fixed date in formula to a cell location with a changable date
    By Car7os in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-18-2013, 11:26 AM
  4. Formula for change date?
    By codyaaron2 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-11-2009, 03:35 PM
  5. [SOLVED] I need a formula that change all date by putting in one date
    By sherrie@ownacondo.com in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-07-2006, 11:25 AM

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