+ Reply to Thread
Results 1 to 5 of 5

How can I make this dynamic calendar sort data from the future and the past?

  1. #1
    Registered User
    Join Date
    07-14-2020
    Location
    California
    MS-Off Ver
    2016
    Posts
    30

    How can I make this dynamic calendar sort data from the future and the past?

    Hello! I've used VLOOKUP to help find the dates of each item and with the help of other users the calendar is currently set up to show estimated time for future years in regards to the cycle days however since some cycles are 365 years is there a way to show multiple cycles for the future and change dynamically once the time comes to continue showing into the future? Also is there a way to do that with the past times its been purchased to show the estimated time for previous years. I was thinking of possibly using VLOOKUP to find the estimated time for past years and link it to the date in the title of a column or maybe using the same method for adding cycles but subtracting cycles? Although when doing that I dont know how it would react to there possibly being a missing order in the cycle? If there is any way I can elaborate to make things clearer please let me know and thank you for your help!
    Attached Files Attached Files
    Last edited by cheeseleweese; 01-21-2021 at 05:11 PM.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    12,082

    Re: How can I make this dynamic calendar sort data from the future and the past?

    Perhaps we would be in a better position to offer help if there was more information as to how the current values are calculated so that we can get a better idea of what is being attempted.
    For instance it seems that the cycle days for banana should be 561 days as opposed to 1460 days based on the two purchase dates being 6/2/19 and 12/14/20.
    If the value were to be 561, I would suggest adding a column (G3:G12) to the data on the Input Data sheet. The new column could be populated using: =IF(E3="","",E3-INDEX(E4:E$12,MATCH(D3,D4:D$12,0)))
    The cycle days column on the sheet could then be populated using: =IF(A2="","",IF(C2="Missing", 365,INDEX('Input Data'!G$3:G$12,MATCH(A2,'Input Data'!D$3:D$12,0))))
    However I don't have enough information to know how to calculate the 1460.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    7,841

    Re: How can I make this dynamic calendar sort data from the future and the past?

    In G2:

    Please Login or Register  to view this content.
    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

    Is it are you looking for?
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,938

    Re: How can I make this dynamic calendar sort data from the future and the past?

    Any chance of getting you to close out your previous thread??

    https://www.excelforum.com/excel-for...red-again.html
    Glenn



  5. #5
    Registered User
    Join Date
    07-14-2020
    Location
    California
    MS-Off Ver
    2016
    Posts
    30

    Re: How can I make this dynamic calendar sort data from the future and the past?

    Hi when I tried using the function with the curly brackets it left everything blank on the document I'm using I also changed the range of the columns so it would read through all of them so I'm not sure if that might have been the problem, are there any recommendations you'd have that I try to do to fix it?

+ 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. Highlight cells depending on dates in past or in future
    By slock92 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-10-2017, 07:32 AM
  2. Sort data within past week
    By thom2467 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-14-2016, 08:50 AM
  3. [SOLVED] Does anyone have code to determine holidays in the past and future?
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-03-2016, 08:23 PM
  4. Past and future date list
    By unome tom in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-11-2015, 04:06 PM
  5. Calcualte Future or past date and....
    By rlcohen70 in forum Excel General
    Replies: 8
    Last Post: 08-14-2008, 01:16 AM
  6. How do I Calculate a future or past date in Excel?
    By MosMash in forum Excel General
    Replies: 2
    Last Post: 07-06-2005, 06:05 PM
  7. Highlight past & future events
    By rediproof in forum Excel General
    Replies: 4
    Last Post: 05-26-2005, 11:01 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