Search the next date from formula TODAY

[kayza]

Hi Excel Master

I'm pretty stumped so I'm hoping someone can help!

Let's say the current day and date when using the formula =TODAY() is Monday, 01/11/2017, and I place that formula in cell A1

In cell C1, I want to display the date for Friday (from the date I illustrated above then the result I want is 5).
although the date in cell A1 changes according to the current date, the value in cell C1 still displays 5, and if the current date is equal to 5, then the value in cell C1 will change automatically generate the date for the next Friday. which is 12. and so on.

hopefully the above explanation can be understood
Thank you in advance

 

[AlanY]

not sure if this is what you're after, this will show next Friday's date

=A1+7-WEEKDAY(A1+1)

 

[kayza]

Hi AlanY, Thanks alot for your help
I have make some modifications from the formula you've given:
=TEXT(A1+9-WEEKDAY(A1);"d")


but unfortunetely, I made a critical mistake when giving an example of the current date,
I thought that by giving my own date example (not computer system date), it will make me more easier to apply into my workbook, but I was wrong, and I'm sorry


And since the first day in my workplace is Friday, so i guess the standard WeekDay formula couldn't answered my question. And, once again I'm pretty stumped here, so i tried to combined it with IF functions to change the first day become Friday. Although this combinations give me the value what I'm looking for, but it's look too long and also rough.
and here it is :
=TEXT(A1+9-IF(TEXT(WEEKDAY(A1);"d")="6";1;IF(TEXT(WEEKDAY(A1);"d")="5";7;IF(TEXT(WEEKDAY(A1);"d")="4";6;IF(TEXT(WEEKDAY(A1);"d")="3";5;IF(TEXT(WEEKDAY(A1);"d")="2";4;IF(TEXT(WEEKDAY(A1);"d")="1";3;2))))))-1;"d")




so, Is there any idea how to make it simple and better?
Any help would be appreciated


Thank you in advance

 

[Tetra201]

Try this:

=DAY(A1+8-WEEKDAY(A1,15))

 

[kayza]

Hi Tetra201,


This is absolute simple and perfect.
It works amazingly fast and save me a lot of time.
Once again thank you very much.


Best regards,
kayza