ExcelBanter - Need help on formula!!

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Need help on formula!! (https://www.excelbanter.com/excel-discussion-misc-queries/63371-need-help-formula.html)

Need help on formula!!

I'm trying to create a formula and need help or hints on how to do this.
What I would like to do is: in cell A1 have the word PAYDAY appear on certain
days each month for 1 year. I know that with the 'if' formula you can only
have 7 nested 'if' statements. I need a way to have 24 references (2 days a
month). Any help or questions on this is highly appreciated.

--
Thanks,
T_Sr

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

Need help on formula!!

Travis via OfficeKB.com schreef:
I'm trying to create a formula and need help or hints on how to do this.
What I would like to do is: in cell A1 have the word PAYDAY appear on certain
days each month for 1 year. I know that with the 'if' formula you can only
have 7 nested 'if' statements. I need a way to have 24 references (2 days a
month). Any help or questions on this is highly appreciated.

Specify the dates on another sheet and make a reference to it with VLOOKUP

Need help on formula!!

The simplest solution, I think, doesn't require a formula. All you need to
know is how to format a column of cells so the date shows as you like and
what fill means.

1. format the date column.
2. enter the first date in the top cell.
3. fill down as far you need.
4. in a new column to the left or right type in "payday" next to the day you
get paid
any given week.
5. now select a range of cells in the "payday" column in groups of 7 or 14 or
however many days there are to a pay period with the first cell in the
range
being the cell in which you typed "payday" and the last cell being the
day before
your next "payday".
6. fill down that range as far as you like.

Done.

Provided that you don't have to do anything complicated overall, you don't
need a complicated solution.

"Travis via OfficeKB.com" wrote:

I'm trying to create a formula and need help or hints on how to do this.
What I would like to do is: in cell A1 have the word PAYDAY appear on certain
days each month for 1 year. I know that with the 'if' formula you can only
have 7 nested 'if' statements. I need a way to have 24 references (2 days a
month). Any help or questions on this is highly appreciated.

--
Thanks,
T_Sr

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

Need help on formula!!

Payday comes on the 1st and 15th, but if one of those days is on a weekend
the payday is the friday before that weekend. I have to have certain days
for each month depending on if payday is on a weekend or not.

Sandy Mann wrote:
Travis,

What determins when it is payday? Is it every two weeks? Certain weekdays
after certain dates in the month? Or what?

I'm trying to create a formula and need help or hints on how to do this.
What I would like to do is: in cell A1 have the word PAYDAY appear on
[quoted text clipped - 4 lines]
a
month). Any help or questions on this is highly appreciated.

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1

Need help on formula!!

Travis,

Here is a compilation of the two posts I replied to you other post in the
*.newusers* NG.

Start of 1st Post
*************************************************

=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.

**************************************************

Start of 2nd post
************************************************** *

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

"T_Sr via OfficeKB.com" <u17260@uwe wrote in message
news:59fb4d73671c1@uwe...
Payday comes on the 1st and 15th, but if one of those days is on a weekend
the payday is the friday before that weekend. I have to have certain days
for each month depending on if payday is on a weekend or not.

Sandy Mann wrote:
Travis,

What determins when it is payday? Is it every two weeks? Certain weekdays
after certain dates in the month? Or what?

I'm trying to create a formula and need help or hints on how to do this.
What I would like to do is: in cell A1 have the word PAYDAY appear on
[quoted text clipped - 4 lines]
a
month). Any help or questions on this is highly appreciated.

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200601/1