Finding Friday

  • Thread starter Thread starter mlv
  • Start date Start date
M

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,
 
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
 
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
 
Bernie, so easy when you know how!

Bernie Deitrick said:
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
Click to expand...
 
Back
Top