• 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.

Dynamic/Automate Dates

A

AZExcel

Member
Thanks in advance for any help that is provided.


I have a project that I am working on that requires several things to happen

The format is set up to when a date is entered in a primary cell,


up to 5 rows below which represent up to 5yrs( if applicable) should populate but of course this depends on the date... ie if 3/1/2009 is entered the following should populate descending:


03/01/2012 current date

3/2/2011 2/29/2012

3/2/2010 3/1/2011

3/2/2009 3/1/2010


As mentioned any help would be appreciated
 
Hi ,


Can you clarify as to why the dates should be 03/01/2012 for 2012 , and 03/02/2011 for 2011 ? Why not 03/01/2011 ?


Narayan
 
Sure Narayan


The dates are ascending from the lower rows.


We are trying to represent approximately a full year from the primary date and the primary day and months should always display in the left column with the years ascending.


I apologize for the initial post that did not represent the problem correctly.

below is how the dates should populate


3/1/2012 4/9/2012

3/1/2011 2/29/2012

3/1/2010 2/28/2011

3/1/2009 2/28/2010
 
Hi ,


Thanks for the clarification ; just one more point :


Will you be doing this for several columns , or is it for just one column ?


Suppose I assume that the date entry is in , say K1 , should these dates be filled in , in the cells L1 , K2 and L2 , K3 and L3 , K4 and L4 ? Don't you want the fifth year to be included ?


Narayan
 
Narayank991


To answer your question the date entry would be in a single cell so lets say K1

and the populating data would be in K2-l2,K3-L3 etc to the 5th row if applicable.


with regards to your question about the 5th year, in the example the date 03/1/2009 is < than 5 years ago so the dates would be approx 3 years or rows and the current anniversary date ( so to speak) 03/1/2012 thru the current date would be the 1st row


Thanks in advance for your help
 
Hi ,


Going by the example you have given , will the entry in cell K1 be 03/01/2012 or 03/01/2009 ?


If it is 03/01/2012 , then how do we know that the dates filling in should stop with 03/01/2009 ?


Narayan
 
Narayank991,


The number entered would be 03/01/2009


and would populate as follows:


3/1/2012 4/10/2012

3/1/2011 2/29/2012

3/1/2010 2/28/2011

3/1/2009 2/28/2010
 
Hi ,


In the various cells , enter the following formulae :


1. K2

[pre]
Code: 
=IF($K1>=TODAY(),TODAY(),IF(DATE(YEAR(TODAY()),3,1)>=TODAY(),TODAY(),DATE(YEAR(TODAY()),3,1)))
2.  L2

[pre][code]=TODAY()
3.  K3

=IFERROR(IF(DATE(YEAR($K2)-1,3,1)<$K$1,"",DATE(YEAR($K2)-1,3,1)),"")
[/pre]
4. L3

=IF(K3="","",K2-1)[/code][/pre]
Copy the formulae in 3 and 4 downwards as far as you want.


Narayan
 
If your reference date is in K1

K2: =DATE(YEAR(K1)-1,MONTH(K1),DAY(K1))

L2: =K1-1

Copy K2:L2 down 5 rows
 
Good Morning,


Thank you for the replies. I will try the recommendations this evening after work and post the outcome.


As mentioned I appreciate the responses and help
 
NARAYANK991


Your formula works perfectly except the input date will not always be 03/01/.


It could be any date thru out the year. The month and day in the self population should be consistent with the input date.


I tried altering your formula to say


=IF($K1>=TODAY(),TODAY(),IF(DATE(YEAR(TODAY()),K1,K1)>=TODAY(),TODAY(),DATE(YEAR(TODAY()),K1,K1)))


but I get a error
 
Hi ,


The following formulae should do the job :

[pre]
Code: 
In K2 : =IF(K1>=TODAY(),DATE(YEAR(TODAY())-1,MONTH(K1),DAY(K1)),IF(DATE(YEAR(TODAY()),MONTH(K1),DAY(K1))>TODAY(),DATE(YEAR(TODAY())-1,MONTH(K1),DAY(K1)),DATE(YEAR(TODAY()),MONTH(K1),DAY(K1))))

In L2 : =TODAY()

In K3 : =IFERROR(IF(DATE(YEAR(K2)-1,MONTH(K1),DAY(K1))<$K$1,"",DATE(YEAR(K2)-1,MONTH(K1),DAY(K1))),"")

In L3 : =IF(K3="","",K2-1)
[/pre]

The formulae in K3 and L3 are to be copied down.


Narayan
 
Narayan,


Thank you very much. You are correct it works perfectly


I was attempting to work with the date,year, month functions as you have demonstrated but was not able to figure out how to work the "today" into the process to return the current year.


Your formula(s) demonstrate a level of mastery that I hope to get to some day.


can you recommend a book or resource (other than this awesome forum of course) to help me.


Again thank you very much
 
Hello Hui,


Your formula gave the following result using 03/09/2009


3/9/2009

3/9/2008 3/8/2009

3/9/2007 3/8/2008

3/9/2006 3/8/2007

3/9/2005 3/8/2006

3/9/2004 3/8/2005


This is what I needed for the project.


3/9/2009

3/9/2012 4/11/2012

3/9/2011 3/8/2012

3/9/2010 3/8/2011

3/9/2009 3/8/2010


I appreciate your help and time
 
Hi ,


I think the best books on Excel are by the well-known experts - John Walkenbach , Bill Jelen , Debra Dalgleish ,...


These authors have their Internet presence , which you might already be aware of :


1. http://spreadsheetpage.com/index.php/blog/


2. http://www.mrexcel.com/


3. http://www.debradalgleish.com/


In addition , there are several other experts such as Harlan Grove , Tom Urtis , Bob Umlas , Jan Karel Pieterse. You can read more about them here :


1. http://www.allexperts.com/ep/1059-49857/Excel/Bob-Umlas.htm


2. http://www.allexperts.com/ep/1059-84102/Excel/Jan-Karel-Pieterse.htm


Narayan
 
I need to prepare debtors list with recurring due dates. The credit period gave is 90 days from invoicing. I want to monitor the customers who fall in the category of below 90 days credit and who fall in category of above 90 days.

Please help me
 
You must log in or register to reply here.
Back
Top