References for a whole year
 

I am trying to have a single cell (A1) show the word "payday" when 2
different days each month comes around (the 1st and 15th) for the whole year.
I have a few months already set up using 'if/and' statements like this:
A1 =(IF(AND(A200=1,B200=13),"P * A * Y * D * A * Y",IF
(AND(A201=2,B201=1),"P * A * Y * D * A * Y",IF(AND(A202=2,B202=15),
"P * A * Y * D * A * Y",IF(AND(A203=3,B202=1),"P * A * Y * D
* A * Y",IF(AND(A204=3,B204=15),"P * A * Y * D * A * Y",IF(AND
(A205=3,B205=31),"P * A * Y * D * A * Y",IF(AND(A206=4,B206=14),"P
* A * Y * D * A * Y","")))))))).

A200-206 gives the month 1-12 for the year.
B200-206 gives the day of each month.

Being fairly new to this, is there an easier way to achieve this result? If
so please let me know, and if you have any questions on this posting let me
know.

Thanks for any help,
T_Sr

--
Message posted via http://www.officekb.com

References for a whole year
 

Hi

Your request for 1st and 15th isn't borne out by the tests within your
formula.
If Payday is on a Friday (which one or two of the dates suggest), and
with the first paydate in 2006 being 13th January 2006, then enter that
date in cell A2 and the following formula in cell A1

=IF(MOD(TODAY()-A2,14)=0,"P * A * Y * D * A * Y","")

--
Regards

Roger Govier


"T_Sr via OfficeKB.com" <u17260@uwe wrote in message
news:59ebaf68d6704@uwe...
I am trying to have a single cell (A1) show the word "payday" when 2
different days each month comes around (the 1st and 15th) for the
whole year.
I have a few months already set up using 'if/and' statements like
this:
A1 =(IF(AND(A200=1,B200=13),"P * A * Y * D * A *
Y",IF
(AND(A201=2,B201=1),"P * A * Y * D * A *
Y",IF(AND(A202=2,B202=15),
"P * A * Y * D * A * Y",IF(AND(A203=3,B202=1),"P * A * Y
* D
* A * Y",IF(AND(A204=3,B204=15),"P * A * Y * D * A *
Y",IF(AND
(A205=3,B205=31),"P * A * Y * D * A *
Y",IF(AND(A206=4,B206=14),"P
* A * Y * D * A * Y","")))))))).

A200-206 gives the month 1-12 for the year.
B200-206 gives the day of each month.

Being fairly new to this, is there an easier way to achieve this
result? If
so please let me know, and if you have any questions on this posting
let me
know.

Thanks for any help,
T_Sr

--
Message posted via http://www.officekb.com

References for a whole year
 

I don't know about easier but:

=IF(DAY(TODAY())=15,IF(WEEKDAY(DATE(YEAR(TODAY()) ,MONTH(TODAY())+1,1),2)5,DATE(YEAR(TODAY()),MONTH (TODAY())+1,1)-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),2)-5),DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)),IF(WEEK DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)5,DAT E(YEAR(TODAY()),MONTH(TODAY()),15)-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)-5),DATE(YEAR(TODAY()),MONTH(TODAY()),15)))

will return the next payday and on the 1st or 15th of the month will
automatically update itself to the next payday.

--
HTH

Sandy

with @tiscali.co.uk


"T_Sr via OfficeKB.com" <u17260@uwe wrote in message
news:59ebaf68d6704@uwe...
I am trying to have a single cell (A1) show the word "payday" when 2
different days each month comes around (the 1st and 15th) for the whole
year.
I have a few months already set up using 'if/and' statements like this:
A1 =(IF(AND(A200=1,B200=13),"P * A * Y * D * A *
Y",IF
(AND(A201=2,B201=1),"P * A * Y * D * A *
Y",IF(AND(A202=2,B202=15),
"P * A * Y * D * A * Y",IF(AND(A203=3,B202=1),"P * A * Y *
D
* A * Y",IF(AND(A204=3,B204=15),"P * A * Y * D * A *
Y",IF(AND
(A205=3,B205=31),"P * A * Y * D * A *
Y",IF(AND(A206=4,B206=14),"P
* A * Y * D * A * Y","")))))))).

A200-206 gives the month 1-12 for the year.
B200-206 gives the day of each month.

Being fairly new to this, is there an easier way to achieve this result?
If
so please let me know, and if you have any questions on this posting let
me
know.

Thanks for any help,
T_Sr

--
Message posted via http://www.officekb.com

References for a whole year
 

To test out the formula use this one:

=IF(DAY(A1)=15,IF(WEEKDAY(DATE(YEAR(A1),MONTH(A1) +1,1),2)5,DATE(YEAR(A1),MONTH(A1)+1,1)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),2)-5),DATE(YEAR(A1),MONTH(A1)+1,1)),IF(WEEKDAY(DATE(Y EAR(A1),MONTH(A1),15),2)5,DATE(YEAR(A1),MONTH(A1) ,15)-(WEEKDAY(DATE(YEAR(A1),MONTH(A1),15),2)-5),DATE(YEAR(A1),MONTH(A1),15)))

Enter today's date in A1 and the formula will return 13 January 2006 (in
whatever format you use). Next enter the date 15 January 2006 and the
formula will update to 1 February 2006. Continue on entering the 1st or
15th of the month and you will get the next 1st or 15th of the month or the
Friday prior to it if it falls on a weekend.
--
HTH

Sandy

with @tiscali.co.uk

"Sandy Mann" wrote in message
...
I don't know about easier but:

=IF(DAY(TODAY())=15,IF(WEEKDAY(DATE(YEAR(TODAY()) ,MONTH(TODAY())+1,1),2)5,DATE(YEAR(TODAY()),MONTH (TODAY())+1,1)-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY())+1,1),2)-5),DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)),IF(WEEK DAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)5,DAT E(YEAR(TODAY()),MONTH(TODAY()),15)-(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)-5),DATE(YEAR(TODAY()),MONTH(TODAY()),15)))

will return the next payday and on the 1st or 15th of the month will
automatically update itself to the next payday.

--
HTH

Sandy

with @tiscali.co.uk


"T_Sr via OfficeKB.com" <u17260@uwe wrote in message
news:59ebaf68d6704@uwe...
I am trying to have a single cell (A1) show the word "payday" when 2
different days each month comes around (the 1st and 15th) for the whole
year.
I have a few months already set up using 'if/and' statements like this:
A1 =(IF(AND(A200=1,B200=13),"P * A * Y * D * A *
Y",IF
(AND(A201=2,B201=1),"P * A * Y * D * A *
Y",IF(AND(A202=2,B202=15),
"P * A * Y * D * A * Y",IF(AND(A203=3,B202=1),"P * A * Y *
D
* A * Y",IF(AND(A204=3,B204=15),"P * A * Y * D * A *
Y",IF(AND
(A205=3,B205=31),"P * A * Y * D * A *
Y",IF(AND(A206=4,B206=14),"P
* A * Y * D * A * Y","")))))))).

A200-206 gives the month 1-12 for the year.
B200-206 gives the day of each month.

Being fairly new to this, is there an easier way to achieve this result?
If
so please let me know, and if you have any questions on this posting let
me
know.

Thanks for any help,
T_Sr

--
Message posted via http://www.officekb.com