Converting Dates to Week Ending time period

[drew]

I am trying to convert a specific date to a Week Ending value. The week
starts on Saturday and ends on Friday for this example.

So.. any value from 6/14 through 6/20 would return a value of week ending
6-20-2008. Any suggestions? Thanks!

 

[vezerid]

One way: for a date in A2, next Friday:

=CEILING(A2+1,7)-1

HTH
Kostis Vezerides

 

[Tim879]

try this formula. It assumes the date you want to convert (i.e. 6/14)
is in cell A14. It will always return the Friday following the date
you enter. If you enter a Friday, it will return the same date.

=+A14+7-IF(WEEKDAY(A14,1)=6,1,IF(WEEKDAY(A14,1)=7,2,WEEKDAY(A14,1)+2))

 

[Gary''s Student]

=A1-WEEKDAY(A1+1,1)+7

 

[Sandy Mann]

I like it!
--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[drew]

This worked! Thanks folks!!

try this formula. It assumes the date you want to convert (i.e. 6/14)
is in cell A14. It will always return the Friday following the date
you enter. If you enter a Friday, it will return the same date.

=+A14+7-IF(WEEKDAY(A14,1)=6,1,IF(WEEKDAY(A14,1)=7,2,WEEKDAY(A14,1)+2))

 

[Ron Rosenfeld]

One way: for a date in A2, next Friday:

=CEILING(A2+1,7)-1

HTH
Kostis Vezerides

That only works with the 1900 date system. With the 1904 date system, commonly
used on Mac's, it will not give the correct answer.
--ron

 

[Ron Rosenfeld]

I am trying to convert a specific date to a Week Ending value. The week
starts on Saturday and ends on Friday for this example.

So.. any value from 6/14 through 6/20 would return a value of week ending
6-20-2008. Any suggestions? Thanks!

=A1+7-WEEKDAY(A1+1)


--ron

 

[vezerid]

You are right Ron, thanks for the pointer.

Kostis