+ Reply to Thread
Results 1 to 7 of 7

Formula to change value on certain date

  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

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,788

    Re: Formula to change value on certain date

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

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

    Re: Formula to change value on certain date

    File attached

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Formula to change value on certain date

    you may try this

    for month

    =DATEDIF(B1,TODAY(),"ym")

    for date

    =DATEDIF(B1,TODAY(),"md")

    or if you need number of days

    =DATEDIF(B1,TODAY(),"ym")*30+DATEDIF(B1,TODAY(),"md")
    Click just below left if it helps, Boo?ath?

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

    Re: Formula to change value on certain date

    OK, I see where that would calculate the difference and would essentially allow the change to occur when the month/date difference is 0. Now, I need to have it do that AND increase at specific milestones according to the value in cell B2.
    Last edited by TheTubaGeek; 08-14-2014 at 08:18 AM.

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

    Re: Formula to change value on certain date

    OK, I guess my statement was a little confusing ...

    It already updates on milestone dates, but I want it to update every year to the appropriate value and then increase at the specific milestones.

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

    Re: Formula to change value on certain date

    Does anyone else have some input, or would what boopathiraja posted work for what I want to do? If I have to use a macro, I will, but I'd rather not if I can avoid it.

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

    Re: Formula to change value on certain date

    OK, after looking around, I found something to try, but when I enter the formula, I get an error of too many arguments (carriage returns entered to help with following the logic):

    =IF
    (TODAY()>=DATE(YEAR(B1)+10,MONTH(b1),DAY(b1)),160,
    TODAY()>=DATE(YEAR(B1)+5,MONTH(b1),DAY(b1)),120,
    TODAY()>=DATE(YEAR(B1)+2,MONTH(B1),DAY(B1)),80,
    TODAY()>=DATE(YEAR(B1)+1,MONTH(B1),DAY(B1)),40)))

    I try to get some help and the result comes up as "Volatile" when I look at the function arguments.

    EDIT: I figured out what I did wrong and was able to fix it.
    Last edited by TheTubaGeek; 09-04-2014 at 09:30 AM.

+ 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. 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. 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