Adjusting Date Results

Joe,

Try this

=A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JoeKazz" <[email protected]> wrote in message
news:[email protected]...
> I am using Excel 2002. I would like to understand how to have a date
result
> from a formula change the date to a Monday, should the result be a
Saturday
> or Sunday date. For instance, my return date should be sixteen days from
> today. But if the return date is Saturday or Sunday, make the return date
a
> Monday. I fooled around to get a formula to work, but only for five rows.
> Then, the formula returned a normal X + 16 result.
> A B C
> 1 08/01/05 08/17/05 Wednesday
> Formula ColumnC:
> A1+16
> Format Column C:
dddd
> Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> The formula did not work with Saturday/Sunday in quotes " ". I also
> "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
formula
> only worked for dates 8/1/05 through 8/5/05, then the result was always
the
> date plus 16.

Bob:

Oh my God, thank you!!!! It works, it works, it works!!!

"Bob Phillips" wrote:

> Joe,
>
> Try this
>
> =A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JoeKazz" <[email protected]> wrote in message
> news:[email protected]...
> > I am using Excel 2002. I would like to understand how to have a date
> result
> > from a formula change the date to a Monday, should the result be a
> Saturday
> > or Sunday date. For instance, my return date should be sixteen days from
> > today. But if the return date is Saturday or Sunday, make the return date
> a
> > Monday. I fooled around to get a formula to work, but only for five rows.
> > Then, the formula returned a normal X + 16 result.
> > A B C
> > 1 08/01/05 08/17/05 Wednesday
> > Formula ColumnC:
> > A1+16
> > Format Column C:
> dddd
> > Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> > The formula did not work with Saturday/Sunday in quotes " ". I also
> > "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
> formula
> > only worked for dates 8/1/05 through 8/5/05, then the result was always
> the
> > date plus 16.
>
>
>

A general formula for moving past the weekend
=IF(WEEKDAY(A1,2)>5,A1-WEEKDAY(A1,2)+8,A1)
or
=WORKDAY(WORKDAY(A1,-1),1)

And for your specific need of +16ish days
=IF(WEEKDAY(A1+16,2)>5,A1-WEEKDAY(A1,2)+8+16,A1+16)
or
=WORKDAY(WORKDAY(A1+16,-1),1)

These remove the need for your helper columns
Not sure what you are saying about defined names. If this does not solve
your problem then please post back

I am not sure which of the equations, I have offered, is most efficient
and would appriciate any comments about that.

hth RES

On Tue, 9 Aug 2005 17:26:56 +0100, [email protected] wrote:

>=WORKDAY(WORKDAY(A1+16,-1),1)

Or, a bit simpler:

=WORKDAY(A1+15,1)




--ron

Ron
nice - it's always good learning when you miss the obvious

is there a simple option if you want to move Sat and Sum forward to Mon
eg. better then
>=WORKDAY(WORKDAY(A1,-1),1)

cheers RES

On Wed, 10 Aug 2005 11:43:27 +0100, [email protected] wrote:


>
>is there a simple option if you want to move Sat and Sum forward to Mon
>eg. better then
>>=WORKDAY(WORKDAY(A1,-1),1)

Yep: =workday(A1-1,1)


>
>cheers RES



--ron

On Wed, 10 Aug 2005 11:43:27 +0100, [email protected] wrote:


>
>is there a simple option if you want to move Sat and Sum forward to Mon
>eg. better then
>>=WORKDAY(WORKDAY(A1,-1),1)

Yep: =workday(A1-1,1)


>
>cheers RES



--ron

Ron
nice - it's always good learning when you miss the obvious

is there a simple option if you want to move Sat and Sum forward to Mon
eg. better then
>=WORKDAY(WORKDAY(A1,-1),1)

cheers RES

On Tue, 9 Aug 2005 17:26:56 +0100, [email protected] wrote:

>=WORKDAY(WORKDAY(A1+16,-1),1)

Or, a bit simpler:

=WORKDAY(A1+15,1)




--ron

A general formula for moving past the weekend
=IF(WEEKDAY(A1,2)>5,A1-WEEKDAY(A1,2)+8,A1)
or
=WORKDAY(WORKDAY(A1,-1),1)

And for your specific need of +16ish days
=IF(WEEKDAY(A1+16,2)>5,A1-WEEKDAY(A1,2)+8+16,A1+16)
or
=WORKDAY(WORKDAY(A1+16,-1),1)

These remove the need for your helper columns
Not sure what you are saying about defined names. If this does not solve
your problem then please post back

I am not sure which of the equations, I have offered, is most efficient
and would appriciate any comments about that.

hth RES

Bob:

Oh my God, thank you!!!! It works, it works, it works!!!

"Bob Phillips" wrote:

> Joe,
>
> Try this
>
> =A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JoeKazz" <[email protected]> wrote in message
> news:[email protected]...
> > I am using Excel 2002. I would like to understand how to have a date
> result
> > from a formula change the date to a Monday, should the result be a
> Saturday
> > or Sunday date. For instance, my return date should be sixteen days from
> > today. But if the return date is Saturday or Sunday, make the return date
> a
> > Monday. I fooled around to get a formula to work, but only for five rows.
> > Then, the formula returned a normal X + 16 result.
> > A B C
> > 1 08/01/05 08/17/05 Wednesday
> > Formula ColumnC:
> > A1+16
> > Format Column C:
> dddd
> > Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> > The formula did not work with Saturday/Sunday in quotes " ". I also
> > "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
> formula
> > only worked for dates 8/1/05 through 8/5/05, then the result was always
> the
> > date plus 16.
>
>
>

Joe,

Try this

=A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JoeKazz" <[email protected]> wrote in message
news:[email protected]...
> I am using Excel 2002. I would like to understand how to have a date
result
> from a formula change the date to a Monday, should the result be a
Saturday
> or Sunday date. For instance, my return date should be sixteen days from
> today. But if the return date is Saturday or Sunday, make the return date
a
> Monday. I fooled around to get a formula to work, but only for five rows.
> Then, the formula returned a normal X + 16 result.
> A B C
> 1 08/01/05 08/17/05 Wednesday
> Formula ColumnC:
> A1+16
> Format Column C:
dddd
> Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> The formula did not work with Saturday/Sunday in quotes " ". I also
> "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
formula
> only worked for dates 8/1/05 through 8/5/05, then the result was always
the
> date plus 16.

On Wed, 10 Aug 2005 11:43:27 +0100, [email protected] wrote:


>
>is there a simple option if you want to move Sat and Sum forward to Mon
>eg. better then
>>=WORKDAY(WORKDAY(A1,-1),1)

Yep: =workday(A1-1,1)


>
>cheers RES



--ron

Ron
nice - it's always good learning when you miss the obvious

is there a simple option if you want to move Sat and Sum forward to Mon
eg. better then
>=WORKDAY(WORKDAY(A1,-1),1)

cheers RES

On Tue, 9 Aug 2005 17:26:56 +0100, [email protected] wrote:

>=WORKDAY(WORKDAY(A1+16,-1),1)

Or, a bit simpler:

=WORKDAY(A1+15,1)




--ron

A general formula for moving past the weekend
=IF(WEEKDAY(A1,2)>5,A1-WEEKDAY(A1,2)+8,A1)
or
=WORKDAY(WORKDAY(A1,-1),1)

And for your specific need of +16ish days
=IF(WEEKDAY(A1+16,2)>5,A1-WEEKDAY(A1,2)+8+16,A1+16)
or
=WORKDAY(WORKDAY(A1+16,-1),1)

These remove the need for your helper columns
Not sure what you are saying about defined names. If this does not solve
your problem then please post back

I am not sure which of the equations, I have offered, is most efficient
and would appriciate any comments about that.

hth RES

Bob:

Oh my God, thank you!!!! It works, it works, it works!!!

"Bob Phillips" wrote:

> Joe,
>
> Try this
>
> =A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "JoeKazz" <[email protected]> wrote in message
> news:[email protected]...
> > I am using Excel 2002. I would like to understand how to have a date
> result
> > from a formula change the date to a Monday, should the result be a
> Saturday
> > or Sunday date. For instance, my return date should be sixteen days from
> > today. But if the return date is Saturday or Sunday, make the return date
> a
> > Monday. I fooled around to get a formula to work, but only for five rows.
> > Then, the formula returned a normal X + 16 result.
> > A B C
> > 1 08/01/05 08/17/05 Wednesday
> > Formula ColumnC:
> > A1+16
> > Format Column C:
> dddd
> > Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> > The formula did not work with Saturday/Sunday in quotes " ". I also
> > "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
> formula
> > only worked for dates 8/1/05 through 8/5/05, then the result was always
> the
> > date plus 16.
>
>
>

Joe,

Try this

=A1+16+(WEEKDAY(A1+16,2)=6)+(WEEKDAY(A1+16,2)>5)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"JoeKazz" <[email protected]> wrote in message
news:[email protected]...
> I am using Excel 2002. I would like to understand how to have a date
result
> from a formula change the date to a Monday, should the result be a
Saturday
> or Sunday date. For instance, my return date should be sixteen days from
> today. But if the return date is Saturday or Sunday, make the return date
a
> Monday. I fooled around to get a formula to work, but only for five rows.
> Then, the formula returned a normal X + 16 result.
> A B C
> 1 08/01/05 08/17/05 Wednesday
> Formula ColumnC:
> A1+16
> Format Column C:
dddd
> Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
> The formula did not work with Saturday/Sunday in quotes " ". I also
> "INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The
formula
> only worked for dates 8/1/05 through 8/5/05, then the result was always
the
> date plus 16.

I am using Excel 2002. I would like to understand how to have a date result
from a formula change the date to a Monday, should the result be a Saturday
or Sunday date. For instance, my return date should be sixteen days from
today. But if the return date is Saturday or Sunday, make the return date a
Monday. I fooled around to get a formula to work, but only for five rows.
Then, the formula returned a normal X + 16 result.
A B C
1 08/01/05 08/17/05 Wednesday
Formula ColumnC:
A1+16
Format Column C: dddd
Formula Column B: =if(c1=Saturday,a1+18,if(c1=Sunday,a1+17,a1+16))
The formula did not work with Saturday/Sunday in quotes " ". I also
"INSERT", "NAME", "DEFINE" for all days Sunday through Monday. The formula
only worked for dates 8/1/05 through 8/5/05, then the result was always the
date plus 16.