calculating a date using the day of the week as a starting point?

I need to calculate the start date of a service based on the day of the week,
i.e. Monday, Tuesday, etc.

For example:
All of our clients are paid weekly. Their paydays vary between Monday and
Thursday. Lets say client "A" starts a service on a Wednesday (12 July
2006), but he/she is paid on a Monday, we would take the start date of their
service as the following Monday (17 July 2006). In other words, we can only
pay clients weekly, starting from their very next payday.

I need to enter a formula to work out the 17th July in the example above.

There is probably a more elegant method but the following if statement
works.
A2 = the weekday of the pay period where 1 = Sunday, 2 = Monday, etc.
(must be a number)
B2 = the first day of actual work
C2
=IF(A2=WEEKDAY(B2),B2,IF(A2>WEEKDAY(B2),B2+A2-WEEKDAY(B2),B2+A2-WEEKDAY(B2)+7))

- John


Simon wrote:
> I need to calculate the start date of a service based on the day of the week,
> i.e. Monday, Tuesday, etc.
>
> For example:
> All of our clients are paid weekly. Their paydays vary between Monday and
> Thursday. Lets say client "A" starts a service on a Wednesday (12 July
> 2006), but he/she is paid on a Monday, we would take the start date of their
> service as the following Monday (17 July 2006). In other words, we can only
> pay clients weekly, starting from their very next payday.
>
> I need to enter a formula to work out the 17th July in the example above.

Hi Simon,

For Monday:
=$A1+MOD(9-WEEKDAY($A1),7)
For Tuesday:
=$A1+MOD(10-WEEKDAY($A1),7)
etc...

HTH
--
AP

"Simon" <Simon@discussions.microsoft.com> a écrit dans le message de news:
26957DD5-0AE9-4701-9593-CDC0DCBDE38A@microsoft.com...
>I need to calculate the start date of a service based on the day of the
>week,
> i.e. Monday, Tuesday, etc.
>
> For example:
> All of our clients are paid weekly. Their paydays vary between Monday and
> Thursday. Lets say client "A" starts a service on a Wednesday (12 July
> 2006), but he/she is paid on a Monday, we would take the start date of
> their
> service as the following Monday (17 July 2006). In other words, we can
> only
> pay clients weekly, starting from their very next payday.
>
> I need to enter a formula to work out the 17th July in the example above.