+ Reply to Thread
Results 1 to 7 of 7

Timeline date column auto update when date changed?

  1. #1
    Registered User
    Join Date
    08-17-2013
    Location
    Milwaukee, USA
    MS-Off Ver
    Excel mac 2011
    Posts
    12

    Timeline date column auto update when date changed?

    Hi there,

    I hope you can help!

    I have a spreadsheet with a column of dates a week apart. This is a timeline/calendar of a project.

    I would like to be able to change this on the fly, and have all dates update in relation (forward or backwards), when one of the date cells is changed (ie a deadline/task is rescheduled)

    Does that make sense?! Many thanks!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Timeline date column auto update when date changed?

    Yes it makes sense. All you have to do is link all the other dates in one way or another to the date you want to change. So if the date you wanted to change is in cell A2 all you have to do is to make sure other dates have some relationship to A2. So another cell might be A2=7 so it is a week ahead. Maybe that one is in A3, then a different cell could be A3+14 so when you change A2 A3 goes ahead 7 days and A3 goes ahead 14 from A3 and 21 from A2.
    You can do the same going backwards.

    To get much more than that you might need to post a workbook with examples of what you want - expected results.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    08-17-2013
    Location
    Milwaukee, USA
    MS-Off Ver
    Excel mac 2011
    Posts
    12
    Quote Originally Posted by Sambo kid View Post
    Yes it makes sense. All you have to do is link all the other dates in one way or another to the date you want to change. So if the date you wanted to change is in cell A2 all you have to do is to make sure other dates have some relationship to A2. So another cell might be A2=7 so it is a week ahead. Maybe that one is in A3, then a different cell could be A3+14 so when you change A2 A3 goes ahead 7 days and A3 goes ahead 14 from A3 and 21 from A2.
    You can do the same going backwards.

    To get much more than that you might need to post a workbook with examples of what you want - expected results.
    Thank you for your reply, I really appreciate it. In this example only a single cell (A2) is changed, but is there a way to make any cell in the column one that can change the timeline? It could be any of the cells that need changing you see - I may need to change the date of the cell A2, but later on the event on A44 has a schedule change.

  4. #4
    Registered User
    Join Date
    08-17-2013
    Location
    Milwaukee, USA
    MS-Off Ver
    Excel mac 2011
    Posts
    12

    Re: Timeline date column auto update when date changed?

    I have attached a workbook of my progress so far.

    I have managed to get the dates going forward to change with the formula (previous cell + 7) for each, but not sure how to get the dates previous to update.
    Attached Files Attached Files

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Timeline date column auto update when date changed?

    with regular formulas you cannot have both a formula and a value in the same cell, one will cancel the other.
    So if you use B1 as your hard coded date and all the rest of the values in B are based on that date, then to change any other date will erase that formula.
    NOW, that being said, you do have formulas that are based on the previous cell like B10 being equal to B9+7, now if you change B10 to 10/30/19, the values below that change, but the formulas that precede that (in B9 back to B2) would not be affected.
    I think to accomplish that you might need a macro or VBA which is a different issue and a sub forum (and I don't have those skills). So you might want to ask an administrator to move the post to that forum if that is what you need.

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,807

    Re: Timeline date column auto update when date changed?

    This could get very complicated if I understand you correctly and there aren't enough rules established. You say any date can change and you want all other dates then to change accordingly (both previous and future dates), and then you say another date may change and you want the dates to change again.
    So if you have 4 tasks, each a week apart starting Jan 1 say, they each have a date of Jan 1, Jan 8, Jan 15, and Jan 22. So suppose you change Jan 8 to Jan 7. You then want Jan 1 to be changed to Dec 31, and the following dates to be reduced by one also. What if today is Jan 3 - would you still want Jan 1 to change to Dec 31?

    Also, in this same example, let's say, after changing the second date to Jan 7 you changed the 3rd date to Jan 16 (it was Jan 14 because of the change you previously made to the Jan 8 date). Then would you want the original Jan 8 date (which is now Jan 7) to change to Jan 9 (and the Dec 31 date to Jan 2)?

    If the simple rule is that any time any date changes, then ALL dates change by the same amount regardless of what was changed before and regardless if some dates are in the past, then I think we can create a file that does that.

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Timeline date column auto update when date changed?

    After giving it more thought I think it can be done with formulas BUT my vision would involve a helper column. Are you ok with that?
    AND, Gregb makes some good points about dates that you could want to avoid which would complicate things. So you'd need to decide that too.

+ 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. Auto update all dates in a column to todays date on workbook open
    By smorri25 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-01-2016, 03:12 AM
  2. Replies: 8
    Last Post: 05-03-2016, 12:22 PM
  3. [SOLVED] Auto Date update to replace old date in data sheet via userform
    By Raylou in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-27-2014, 12:18 PM
  4. Automatically update date when a range of data is changed
    By rcvanriet in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 09-12-2012, 12:58 PM
  5. auto update the date edited on column
    By SelinaT in forum Excel General
    Replies: 1
    Last Post: 02-17-2009, 01:56 AM
  6. [SOLVED] Can excel update date when document last changed?
    By Nic in forum Excel General
    Replies: 1
    Last Post: 07-26-2006, 02:44 PM
  7. Replies: 0
    Last Post: 07-26-2006, 10: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