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!

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

=CEILING(A2+1,7)-1

HTH
Kostis Vezerides

On Jun 20, 4:18 pm, drew <d...@discussions.microsoft.com> wrote:
> 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!

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


On Jun 20, 9:18 am, drew <d...@discussions.microsoft.com> wrote:
> 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-WEEKDAY(A1+1,1)+7
--
Gary''s Student - gsnu200793

I like it!
--

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

(E-Mail Removed)
Replace @mailinator.com with @tiscali.co.uk


"vezerid" <(E-Mail Removed)> wrote in message
news:477f0304-5775-4a42-b34e-(E-Mail Removed)...
> One way: for a date in A2, next Friday:
>
> =CEILING(A2+1,7)-1
>
> HTH
> Kostis Vezerides
>
> On Jun 20, 4:18 pm, drew <d...@discussions.microsoft.com> wrote:
>> 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!
>
>

This worked! Thanks folks!!

"Tim879" wrote:

> 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))
>
>
> On Jun 20, 9:18 am, drew <d...@discussions.microsoft.com> wrote:
> > 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!
>
>

On Fri, 20 Jun 2008 06:29:46 -0700 (PDT), vezerid <(E-Mail Removed)> wrote:

>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

On Fri, 20 Jun 2008 06:18:01 -0700, drew <(E-Mail Removed)>
wrote:

>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

You are right Ron, thanks for the pointer.

Kostis

On Jun 20, 5:21 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Fri, 20 Jun 2008 06:29:46 -0700 (PDT),vezerid<veze...@act.edu> wrote:
> >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