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

how to increment a date by one year which should lie on nearest friday in excel

V

vaibhav tandon

Member
Hello team,

I was making a formula to calculate the date which should lie on nearest friday next year. I was able to make a formula that converts the date to nearest friday in the current or any given year but not able to make it for the next year. any help would be appreiciated.

Formula for current year friday conversion - =D10- WEEKDAY(D10,3)+IF(WEEKDAY(D10,3)>4,11,4)

Column D has the dates which needs to be converted to friday one hear from now.

thanks
Vaibhav
 
Dear Vaibhav

This formula seems to work.

=DATE(YEAR(D2)+1,MONTH(D2),DAY(D2))+MOD(6-WEEKDAY(DATE(YEAR(D2)+1,MONTH(D2),DAY(D2))),7)
 
Hi Jake and Som,

Thanks for your precious help!

Jake your formula is not working for me but Som's works perfectly fine.

Again thanks for all the help. Happy posting!
 
Hi Vaibhav ,

It is good that your problem has been resolved , but I am not sure what is the take away from the question and its answers ; I would like to put down some explanation , but it will take some time.

I would like who ever has posted solutions to think over the results returned when there are input dates such as :

February 29 , 2000

February 28 , 2003

What would be the exact date 1 year away from these dates ?

Narayan
 
Hi Vaibhav ,

May or may not be , depending on how you define the period of one year.

I am only pointing out that you should be aware that using logic and an appreciation of how that is to be translated to Excel formulae is more important that just getting answers from a forum.

Suppose we take February 29 of any leap year ; clearly the next year cannot be a leap year. Thus February 28 of the next year , which is returned by the EDATE(date,12) is not one year from the given date. You need to decide whether this is acceptable. Irrespective of this , if February 28 of the next year , as returned by the EDATE function is a Friday , you need to decide whether this is correct , or 7 days from this date , is correct for your purposes.

Narayan
 
You must log in or register to reply here.
Back
Top