Mieke has attended:
Excel VBA Intro Intermediate course
Taking Minutes course
Date formula - looking to return a weekday in 3/6 months in the
Hi there
I need to calculate an end date which is my start date + 6 months, but I want excel to return the Friday before this end date.
So far my e start date in cell C10 which 19/03/2012. I've calculated my end date in D10 as =DATE(YEAR(C10),MONTH(C10)+6,DAY(C10)-1). This returns the date of 18/09/2012 which is perfect except I'd like the Friday before which is 14/09/2012. I've tried using an if and a weekday but can't seem to get it right
Could you help me please?
Thank you!
RE: Date formula - looking to return a weekday in 3/6 months in
Hello Mieke,
Hope you enjoyed your Microsoft Excel Advanced course with Best STL.
Thank you for your question regarding using a date formula.
I'm not exactly sure why you want the result to be a Friday and also you have not provided sufficient information for me to reply with an exhaustive formula that covers all angles.
So, what I have done is provide you with a formula that changes the result you are presently getting (Tuesday) to the Friday before. I changed your formula to the following:
=IF(WEEKDAY(C10)=2,DATE(YEAR(C10),MONTH(C10)+6,DAY(C10)-5),DATE(YEAR(C10),MONTH(C10)+6,DAY(C10)-1))
Depending on your reasons for producing the result you require, it is possible that you will need another 6 more nested IFs each of which will examine the weekday number and subtract a value to bring the result to a Friday.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
