Finding Certain Dates
 

I get paid on the 1st and 15th of every month unless that day falls on a
weekend or holiday. If that happens I get paid on the business day prior to
the 1st or 15th. I have a flash that pops up on paydays by nesting the IF
function:

(=(IF(AND(B200=2,C200=15),"P * A * Y * D * A * Y",IF(AND(B201=3,
C201=1),"P * A * Y * D * A * Y",IF(AND(B202=3,C202=15),"P * A *
Y * D * A * Y",IF(AND(B203=3,C202=30),"P * A * Y * D * A * Y",
IF(AND(B204=4,C204=13),"P * A * Y * D * A * Y",IF(AND(B205=5,C205=1)
,"P * A * Y * D * A * Y",IF(AND(B206=5,C206=15),"P * A * Y *
D * A * Y","")))))))).

*B200 through B206= the number for the month of the year, and C200 through
C206= the day of the month that is actually payday.


I can only nest 7 of these and get tired of having to go through and put in
the new dates for paydays to keep the flash popping up. Is there anything
that could possibly do this for me. I know nothing about macros and am
trying to learn how they work. Any help is greatly appreciated.

Travis Sr.

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200702/1

Finding Certain Dates
 

Perhaps try creating a list of paydays.

List all your holiday dates somewhere on the worksheet, say Z1:Z10, then in
A1 enter the next payday, e.g. 15th February 2007 then in A2 enter this
formula copied down the column

=WORKDAY(A1+28-IF(DAY(A1+12)15,DAY(A1+28)-2,DAY(A1+12)),-1,Z$1:Z$10)

This will give you a list of all workdays which you can reference for
another formula, e.g. to give you your payday message on payday itself

=IF(ISNUMBER(MATCH(TODAY(),A1:A100,0)),),"P * A * Y * D * A * Y","")

Note: WORKDAY function is part of Analysis ToolPak add-in. To install, Tools
Add-ins and tick "Analysis ToolPak" box


"T_Sr via OfficeKB.com" wrote:

I get paid on the 1st and 15th of every month unless that day falls on a
weekend or holiday. If that happens I get paid on the business day prior to
the 1st or 15th. I have a flash that pops up on paydays by nesting the IF
function:

(=(IF(AND(B200=2,C200=15),"P * A * Y * D * A * Y",IF(AND(B201=3,
C201=1),"P * A * Y * D * A * Y",IF(AND(B202=3,C202=15),"P * A *
Y * D * A * Y",IF(AND(B203=3,C202=30),"P * A * Y * D * A * Y",
IF(AND(B204=4,C204=13),"P * A * Y * D * A * Y",IF(AND(B205=5,C205=1)
,"P * A * Y * D * A * Y",IF(AND(B206=5,C206=15),"P * A * Y *
D * A * Y","")))))))).

*B200 through B206= the number for the month of the year, and C200 through
C206= the day of the month that is actually payday.


I can only nest 7 of these and get tired of having to go through and put in
the new dates for paydays to keep the flash popping up. Is there anything
that could possibly do this for me. I know nothing about macros and am
trying to learn how they work. Any help is greatly appreciated.

Travis Sr.

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200702/1

Finding Certain Dates
 

Sorry when I said in my first post "this will give you a list of all
workdays......." what i meant was "this will give you a list of all
PAYDAYS......"

"daddylonglegs" wrote:

Perhaps try creating a list of paydays.

List all your holiday dates somewhere on the worksheet, say Z1:Z10, then in
A1 enter the next payday, e.g. 15th February 2007 then in A2 enter this
formula copied down the column

=WORKDAY(A1+28-IF(DAY(A1+12)15,DAY(A1+28)-2,DAY(A1+12)),-1,Z$1:Z$10)

This will give you a list of all workdays which you can reference for
another formula, e.g. to give you your payday message on payday itself

=IF(ISNUMBER(MATCH(TODAY(),A1:A100,0)),),"P * A * Y * D * A * Y","")

Note: WORKDAY function is part of Analysis ToolPak add-in. To install, Tools
Add-ins and tick "Analysis ToolPak" box


"T_Sr via OfficeKB.com" wrote:

I get paid on the 1st and 15th of every month unless that day falls on a
weekend or holiday. If that happens I get paid on the business day prior to
the 1st or 15th. I have a flash that pops up on paydays by nesting the IF
function:

(=(IF(AND(B200=2,C200=15),"P * A * Y * D * A * Y",IF(AND(B201=3,
C201=1),"P * A * Y * D * A * Y",IF(AND(B202=3,C202=15),"P * A *
Y * D * A * Y",IF(AND(B203=3,C202=30),"P * A * Y * D * A * Y",
IF(AND(B204=4,C204=13),"P * A * Y * D * A * Y",IF(AND(B205=5,C205=1)
,"P * A * Y * D * A * Y",IF(AND(B206=5,C206=15),"P * A * Y *
D * A * Y","")))))))).

*B200 through B206= the number for the month of the year, and C200 through
C206= the day of the month that is actually payday.


I can only nest 7 of these and get tired of having to go through and put in
the new dates for paydays to keep the flash popping up. Is there anything
that could possibly do this for me. I know nothing about macros and am
trying to learn how they work. Any help is greatly appreciated.

Travis Sr.

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200702/1