References for a whole year

[T_Sr via OfficeKB.com]

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

 

[Roger Govier]

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","")

 

[Sandy Mann]

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(WEEKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),15),2)>5,DATE(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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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(YEAR(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
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk