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

1. ## 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!

2. ## 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.

3. ## 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?

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

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

5. ## 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?

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