Need help on formula!!

  • Thread starter Travis via OfficeKB.com
  • Start date
T

Travis via OfficeKB.com

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

Bart Snel

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.
Click to expand...
Specify the dates on another sheet and make a reference to it with VLOOKUP
 
B

Boris

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

T_Sr via OfficeKB.com

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 said:
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
Click to expand...
[quoted text clipped - 4 lines]
a
month). Any help or questions on this is highly appreciated.
Click to expand...
Click to expand...

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200601/1
 
S

Sandy Mann

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

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

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(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
[email protected]
[email protected] with @tiscali.co.uk

T_Sr via OfficeKB.com said:
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 said:
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
Click to expand...
[quoted text clipped - 4 lines]
a
month). Any help or questions on this is highly appreciated.
Click to expand...
Click to expand...

--
Thanks,
T_Sr

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/ms-excel/200601/1
Click to expand...
 
Top