Week ending calculation

[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

 

[Glenn]

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

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]

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

=E2-WEEKDAY(E2)+6

Make sure to format the result cell as date.

If that's not right, give an example that demonstrates how this doesn't work,
and what result you want.

 

[Sean Timmons]

=IF(WEEKDAY(E2)=7,DATE(YEAR(E2),MONTH(E2),DAY(E2)+6),
DATE(YEAR(E2),MONTH(E2),DAY(E2)-WEEKDAY(E2)+6))

 

[ryguy7272]

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--

 

[Ron Rosenfeld]

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

=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

 

[Ashish Mathur]

Hi,

Try this

=B5+CHOOSE(WEEKDAY(B5,2),4,3,2,1,0,6,5)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com