Week ending calculation

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

I am working in a sheet where I have a date and I am trying to find the
Friday post date. I am using
=DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6)

It works for everyday unless the day is a Saturday. I've tried several other
formulas (found on the discussion board) and get basically the same results.

Michelle
 
Michelle said:
I am working in a sheet where I have a date and I am trying to find the
Friday post date. I am using
=DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6)

It works for everyday unless the day is a Saturday. I've tried several other
formulas (found on the discussion board) and get basically the same results.

Michelle
Click to expand...


Actually, your formula works fine, but can be simplified to this:

=E2-WEEKDAY(E2)+6

Make sure to format the result cell as date.
 
Glenn said:
Actually, your formula works fine, but can be simplified to this:

=E2-WEEKDAY(E2)+6

Make sure to format the result cell as date.
Click to expand...


If that's not right, give an example that demonstrates how this doesn't work,
and what result you want.
 
=IF(WEEKDAY(E2)=7,DATE(YEAR(E2),MONTH(E2),DAY(E2)+6),
DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6))
 
Not sure what you want? The Friday for the current week?
=TODAY()-WEEKDAY(TODAY()-6)+7
=TODAY()+6-MOD(WEEKDAY(TODAY())+7,7)

HTH,
Ryan--
 
I am working in a sheet where I have a date and I am trying to find the
Friday post date. I am using
=DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6)

It works for everyday unless the day is a Saturday. I've tried several other
formulas (found on the discussion board) and get basically the same results.

Michelle
Click to expand...

=A1+7-WEEKDAY(A1+1)

Will return the next Friday of any date in A1; unless the date is a Friday, in
which case it will return the same date.
--ron
 
Back
Top