I have automatic payments due on the last day of the month, unless the last
day of the month is a weekend or holiday.
I don't know at this time if the payment will be made early - on the last
working day - or postponed - until the next working day.
I have tried to get the WORKDAY function to shift the date for me.
I can read the definition of WORKDAY in a way that indicates this can be
done. Maybe I am reading the definition wrong.

I have a column of biweekly dates including EOMONTH for each month and a
WORKDAY formula that does not work:
A1 = 1/5/2009 =IF(A1=WORKDAY(A1,0),A1,WORKDAY(A1,1
A2 = 1/19/2009 =IF(A2=WORKDAY(A2,0),A2,WORKDAY(A2,1)
A3 = 1/31/2009 =IF(A3=WORKDAY(A3,0),A3,WORKDAY(A3,1)
A4 = 2/2/2009 =IF(A4=WORKDAY(A4,0),A4,WORKDAY(A4,1)
A5 = 2/16/2009 =IF(A5=WORKDAY(A5,0),A5,WORKDAY(A5,1)
A6 = 2/28/2009 =IF(A6=WORKDAY(A6,0),A6,WORKDAY(A6,1)

The way I interpret the WORKDAY function is that if the date is NOT a
workday, the WORKDAY function should find and display the next workday with
argument 1, the last preceding workday with argument -1, and no date change
if argument is 0. In the example above, the formula returns exactly the
same dates as in column A, even though A3 and A6 dates are weekends. If I
replace the = in the formula with <, the weekend dates all are moved to the
next workday, as I need, but all the rest of dates have one day added as
well.

Any help to make this formula work for me would be appreciated, or any other
simple way to move a weekend date to a workday.

--
staplers

Try the below..to skip Saturdays and Sundays. Adjust to suit

=A1+LOOKUP(WEEKDAY(A1),{1,2,7},{1,0,2})

If this post helps click Yes
---------------
Jacob Skaria


"staplers" wrote:

I have automatic payments due on the last day of the month, unless the last
day of the month is a weekend or holiday.
I don't know at this time if the payment will be made early - on the last
working day - or postponed - until the next working day.
I have tried to get the WORKDAY function to shift the date for me.
I can read the definition of WORKDAY in a way that indicates this can be
done. Maybe I am reading the definition wrong.

I have a column of biweekly dates including EOMONTH for each month and a
WORKDAY formula that does not work:
A1 = 1/5/2009 =IF(A1=WORKDAY(A1,0),A1,WORKDAY(A1,1
A2 = 1/19/2009 =IF(A2=WORKDAY(A2,0),A2,WORKDAY(A2,1)
A3 = 1/31/2009 =IF(A3=WORKDAY(A3,0),A3,WORKDAY(A3,1)
A4 = 2/2/2009 =IF(A4=WORKDAY(A4,0),A4,WORKDAY(A4,1)
A5 = 2/16/2009 =IF(A5=WORKDAY(A5,0),A5,WORKDAY(A5,1)
A6 = 2/28/2009 =IF(A6=WORKDAY(A6,0),A6,WORKDAY(A6,1)

The way I interpret the WORKDAY function is that if the date is NOT a
workday, the WORKDAY function should find and display the next workday with
argument 1, the last preceding workday with argument -1, and no date change
if argument is 0. In the example above, the formula returns exactly the
same dates as in column A, even though A3 and A6 dates are weekends. If I
replace the = in the formula with <, the weekend dates all are moved to the
next workday, as I need, but all the rest of dates have one day added as
well.

Any help to make this formula work for me would be appreciated, or any other
simple way to move a weekend date to a workday.

--
staplers

I am sure with the Add-In functions there should be a easy way to do
this...The below formula with MONTH() and WEEDAY() and LOOKUP() will adjust
to next/previous workday......

=IF(MONTH(A1+LOOKUP(WEEKDAY(A1),{1,2,7},{1,0,2}))< MONTH(A1),A1+LOOKUP(WEEKDAY(A1),{1,2,7},{-2,0,-1}),A1+LOOKUP(WEEKDAY(A1),{1,2,7},{1,0,2}))

If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Try the below..to skip Saturdays and Sundays. Adjust to suit

=A1+LOOKUP(WEEKDAY(A1),{1,2,7},{1,0,2})

If this post helps click Yes
---------------
Jacob Skaria


"staplers" wrote:

I have automatic payments due on the last day of the month, unless the last
day of the month is a weekend or holiday.
I don't know at this time if the payment will be made early - on the last
working day - or postponed - until the next working day.
I have tried to get the WORKDAY function to shift the date for me.
I can read the definition of WORKDAY in a way that indicates this can be
done. Maybe I am reading the definition wrong.

I have a column of biweekly dates including EOMONTH for each month and a
WORKDAY formula that does not work:
A1 = 1/5/2009 =IF(A1=WORKDAY(A1,0),A1,WORKDAY(A1,1
A2 = 1/19/2009 =IF(A2=WORKDAY(A2,0),A2,WORKDAY(A2,1)
A3 = 1/31/2009 =IF(A3=WORKDAY(A3,0),A3,WORKDAY(A3,1)
A4 = 2/2/2009 =IF(A4=WORKDAY(A4,0),A4,WORKDAY(A4,1)
A5 = 2/16/2009 =IF(A5=WORKDAY(A5,0),A5,WORKDAY(A5,1)
A6 = 2/28/2009 =IF(A6=WORKDAY(A6,0),A6,WORKDAY(A6,1)

The way I interpret the WORKDAY function is that if the date is NOT a
workday, the WORKDAY function should find and display the next workday with
argument 1, the last preceding workday with argument -1, and no date change
if argument is 0. In the example above, the formula returns exactly the
same dates as in column A, even though A3 and A6 dates are weekends. If I
replace the = in the formula with <, the weekend dates all are moved to the
next workday, as I need, but all the rest of dates have one day added as
well.

Any help to make this formula work for me would be appreciated, or any other
simple way to move a weekend date to a workday.

--
staplers