Finding Friday

[mlv]

I'm using the formula below to find Saturday's date within the same week of
any date entered in A1.

=FLOOR(A1+5,7)

The formula works fine, but I now want to find Friday's date within the same
week of any date in A1.

I understand how the date in A1 (as a serial number) has 5 added to it and
the result is then rounded down to the nearest multiple of 7.

Does this type of formula only work for Saturday, or is there a similar
formula that will find Friday's date instead.

Otherwise, I guess I'll enter: =(FLOOR(A1+5,7)-1)

Thanks,

 

[Bernie Deitrick]

Mike,

For any date between Sunday and Saturday inclusive, this will return the Friday of that week

=A1+6-WEEKDAY(A1)

HTH,
Bernie
MS Excel MVP

 

[Ron@Buy]

Here's a variation of a formula that I use:
=A1-CHOOSE(WEEKDAY(A1,1),2,3,4,5,6,7,1)+7
Enter this in B1 to check the day:
TEXT(WEEKDAY(A1,1),"dddd")
Hope this helps

 

[Ron@Buy]

Bernie, so easy when you know how!

Mike,

For any date between Sunday and Saturday inclusive, this will return the Friday of that week

=A1+6-WEEKDAY(A1)

HTH,
Bernie
MS Excel MVP

 

[mlv]

Thanks Bernie & Ron, I can now find any day of the week that I want!