• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Future date

Thomas Kuriakose

Active Member
Respected Sirs,

Good day to you,

I am not able to figure out why the future date in the attached file does not work if the dates are changed.

Our requirement is to arrive at the future date based on issue date (A2) and the tenure (B2) with respect to todays date and then if the year falls in 2019 we need to take action as in Q2 (yes if in current year and no if not in current year).

When I change the issue date to 2019 I get #N/A in future date.

Firstly kindly guide whether the formula in C2 is correct.

I tried to prepare a schedule with EDATE function, but not able to reference the years in D1 to P1 to automate the table for all cells based on the issue date and tenure.

Thank you very much for your support and guidance always,

very much appreciated,

with regards,
thomas
 

Attachments

  • Dates.xlsx
    11.2 KB · Views: 10
Thomas

You are trying to make an Array based on Today() and a number of months ahead of that, and then lookup the value of A2 in that array
Unless they conicide it will not match

Shouldn't C2 simply be : =EDATE(A2,B2)
 
Respected Sir,

Thank you so much for your guidance and support,

In C2 we need to know what is the future date based on the issue date as of today without providing the previous tenure dates. We need to monitor how many bills with issue date falls in the current year for action. We need the future tenure date.

The last date in N2, 09/06/2019 as in the first case C2 where issue date is 09/06/2009. Similarly O3, 04/20/2020 as in the second case where issue date is 04/20/2010.

Since the issue dates with 2019 was giving an error in C2 down, we prepared the schedule in D2 to P4 to manually check the last dates.

I hope I was able to explain, kindly do let me know if this is not clear.

Thank you very much once again,

with regards,
thomas
 
Thomas,

It appears that Excel is interpreting the first row as text which does not equal the numeric value. One way to correct the formula would be to wrap the portion which references row 1 with the VALUE function.

Hope that helps.

Regards,
Ken
 
Thomas,

Reading back through the post, I realize that the above is not the answer you were looking for. Will the tenure amount always be 24 or will it vary? If it is always 24, then you could simply test the whether the issue date and current year are both even (or odd).

Ken
 
Respected Sir,

Thank you for your support on this query,

The tenure will always be 24.

I am still not able to work out a formula to get the correct date in C2 down. For now I am manually updating the schedule for each issue date and the listing is more than 3000 rows of data.

Thanks once again,

with regards,
thomas
 
Thomas,

Please try the slight modification to your original formula as below:
=EDATE(LOOKUP(TODAY(),EDATE(A2,B2*(ROW($A$2:$A$101)-2))),B2)

Hope that helps.

Regards,
Ken
 
In the attached is the adjustment to the formula in cells Q2:Q3 in line with KenU's suggestion.

There's also a second table below the first, with formulae in the range D9 : P11 which will fill in future dates automatically according to columns A and B; you can change either or both of these columns (Issue Date and Tenure) and the dates will appear in the appropriate cells.
There's also a different formula in the Action column which does away with the need for a Future date column (personally I'd keep this column!)
 

Attachments

  • Chandoo42182Dates.xlsx
    14 KB · Views: 7
Respected Sirs,

Outstanding, thank you so very much for the solution on this query.

This works perfectly.

Very much appreciated,

with regards,
thomas
 
Back
Top