+ Reply to Thread
Results 1 to 4 of 4

Formula Keeps Reverting Back to Old One

  1. #1
    Registered User
    Join Date
    12-01-2020
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    2

    Formula Keeps Reverting Back to Old One

    Hi,

    I have a spreadsheet where I need to have two columns with dates in it, 'Passed' and 'Refresher Required' - the second column needs to be the date three years after the first column.

    I inherited this spreadsheet from someone else, who was using this formula:
    =IF('First Aid Responder Tracker'!D4="", "", DATE(YEAR('First Aid Responder Tracker'!D4)+3,MONTH('First Aid Responder Tracker'!D4),DAY('First Aid Responder Tracker'!D4)

    However, this doesn't automatically update when you sort the columns, so the formula needs to be manually fixed.

    I changed it to this formula:
    (Please note, it wouldn't let me post this with the 'at symbol' because it kept interpreting it as a link, and I'm too new to post links. But in the formula the '*at symbol*' is supposed to be the actual at symbol)
    =IF([*at symbol*Passed]="", "", DATE(YEAR([*at symbol*Passed])+3,MONTH([*at symbol*Passed]),DAY([*at symbol*Passed])))

    This formula works fine, and does exactly what I want it to do! The only problem is, when I save and close the spreadsheet, when I open it back up again, the formula has reverted back to the original formula again.

    Why is it doing this, and how can I stop it? I'm afraid I'm not an Excel expert, so apologies if the answer to this is obvious!

    (For extra info, the spreadsheet has three tabs in it that uses these columns and formula. However, I changed the name of the first column to Passed, Passed1 and Passed2, and changed the formula to match, in the hope that this would fix it. It didn't. There are no errors in the spreadsheet).

    Thank you for your help!

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Formula Keeps Reverting Back to Old One

    Are there any macros in the workbook which might change the formula
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-01-2020
    Location
    UK
    MS-Off Ver
    Excel 2016
    Posts
    2

    Re: Formula Keeps Reverting Back to Old One

    Nope, no macros.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,120

    Re: Formula Keeps Reverting Back to Old One

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us 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. Excel reverting back to old formats.
    By Rinnian in forum Excel General
    Replies: 2
    Last Post: 05-18-2016, 08:27 PM
  2. Unprotecting sheet keeps reverting back to protected mode
    By s0nginmyheart in forum Excel General
    Replies: 1
    Last Post: 09-24-2013, 08:11 AM
  3. Code for reverting cell format back to the original one
    By kairab in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2013, 01:14 AM
  4. Reverting back to original format when deselecting check box
    By achill3s in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-14-2011, 11:14 PM
  5. Excel Colors Reverting back to random palette
    By Nick D in forum Excel General
    Replies: 0
    Last Post: 07-26-2007, 09:51 AM
  6. [SOLVED] Date time formats keep reverting back to default
    By cd in forum Excel General
    Replies: 1
    Last Post: 10-12-2005, 05:05 PM
  7. Changed column width keeps reverting back to original size
    By David Boone in forum Excel General
    Replies: 3
    Last Post: 05-29-2005, 09:05 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