If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
years change to current year | nwg | Worksheet Functions | 5 | January 1st, 2006 03:29 PM |
Outlook show week number(1-52); day of year / days left in year | Cogito.ergo.sum | Calendar | 0 | May 3rd, 2005 09:40 PM |
Adding +1 every year. | Joshy | General Discussion | 4 | March 22nd, 2005 01:33 PM |
Combining two queries or two different table on a same report . | sha | Setting Up & Running Reports | 7 | May 28th, 2004 10:41 AM |
IIF statement for Last Fiscal Year | Liz | New Users | 5 | May 18th, 2004 07:02 PM |