What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to tell
Excel to output the date in the above manner?

Maybe you can just do some math.

I put the 15 of the first month in A1.
Then I put this in B1:
=a1+14

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

Then I selected B1:C1 and dragged across as far as I needed.



Yeah wrote:

What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to tell
Excel to output the date in the above manner?

--

Dave Peterson

Thanks for the prompt response!

I partially understood the math. The part where 14 was added to A1 was fine,
but the next statement confused me. It also gave me a Circular Reference
error while outputting the date as 1/01/1900. As I improvised a copy+paste,
it proceeded adding only 14 days.

The 15th day part is easy, but it also needs to alternate between that and
the last weekday of the month for every column...



"Dave Peterson" wrote in message
...
Maybe you can just do some math.

I put the 15 of the first month in A1.
Then I put this in B1:
=a1+14

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

Then I selected B1:C1 and dragged across as far as I needed.



Yeah wrote:

What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

--

Dave Peterson

I used 3 cells:
A1, B1 and C1.

What did you use?

Yeah wrote:

Thanks for the prompt response!

I partially understood the math. The part where 14 was added to A1 was fine,
but the next statement confused me. It also gave me a Circular Reference
error while outputting the date as 1/01/1900. As I improvised a copy+paste,
it proceeded adding only 14 days.

The 15th day part is easy, but it also needs to alternate between that and
the last weekday of the month for every column...

"Dave Peterson" wrote in message
...
Maybe you can just do some math.

I put the 15 of the first month in A1.
Then I put this in B1:
=a1+14

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

Then I selected B1:C1 and dragged across as far as I needed.



Yeah wrote:

What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

--

Dave Peterson

--

Dave Peterson

Try this - in A1 put your first legitimate date, such as 12/30/05, then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to tell
Excel to output the date in the above manner?

Here's another one:

Enter your first date in A1.

=IF(DAY(A1)=15,WORKDAY(EOMONTH(A1,0)-7,5),DATE(YEAR(A1),MONTH(A1)+1,15))

Copy across as needed. Format the cells as DATE. Requires the ATP be
installed.

Biff

"Yeah" wrote in message
news:9OrGg.701$xk3.554@dukeread07...
What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell Excel to output the date in the above manner?

It also gave me a Circular Reference

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

The "+14" causes every EOM to be the 29th.

Biff

"Dave Peterson" wrote in message
...
I used 3 cells:
A1, B1 and C1.

What did you use?

Yeah wrote:

Thanks for the prompt response!

I partially understood the math. The part where 14 was added to A1 was
fine,
but the next statement confused me. It also gave me a Circular Reference
error while outputting the date as 1/01/1900. As I improvised a
copy+paste,
it proceeded adding only 14 days.

The 15th day part is easy, but it also needs to alternate between that
and
the last weekday of the month for every column...

"Dave Peterson" wrote in message
...
Maybe you can just do some math.

I put the 15 of the first month in A1.
Then I put this in B1:
=a1+14

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

Then I selected B1:C1 and dragged across as far as I needed.



Yeah wrote:

What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays.
However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

--

Dave Peterson

--

Dave Peterson

That was supposed to be B1, but you're right about the 29th.


Biff wrote:

It also gave me a Circular Reference

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

The "+14" causes every EOM to be the 29th.

Biff

"Dave Peterson" wrote in message
...
I used 3 cells:
A1, B1 and C1.

What did you use?

Yeah wrote:

Thanks for the prompt response!

I partially understood the math. The part where 14 was added to A1 was
fine,
but the next statement confused me. It also gave me a Circular Reference
error while outputting the date as 1/01/1900. As I improvised a
copy+paste,
it proceeded adding only 14 days.

The 15th day part is easy, but it also needs to alternate between that
and
the last weekday of the month for every column...

"Dave Peterson" wrote in message
...
Maybe you can just do some math.

I put the 15 of the first month in A1.
Then I put this in B1:
=a1+14

Then I put this in C1:
=DATE(YEAR(C1),MONTH(C1)+1,15)

Then I selected B1:C1 and dragged across as far as I needed.



Yeah wrote:

What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays.
However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I feel like such a turdburglar! The part about the 15th is a little off,
too. So BOTH dates have to be formulas.

The paydays are supposed to be:
1) a) The 15th of every month, or, if it falls on a weekend, b) the
preceding weekday before the 15th
2) The last weekday of the month

"DOR" wrote in message
ups.com...
Try this - in A1 put your first legitimate date, such as 12/30/05, then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

You still need to enter the first date in A1:

Enter in B1:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,5),WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,15)-7,5))

Copy across, format as DATE, requires the ATP be installed.

Now, let me throw a monkey wrench into the mix that you may not have
considered!

What about paydays falling on holidays?

Luckily, the WORKDAY function can account for this. It can accept a third
argument that is a list of holiday dates.

You would create a list of the holiday dates and include a reference to that
list as a third argument:

WORKDAY(arg_1,arg_2,H1:H10)

Biff

"Yeah" wrote in message
news:ppKGg.1078$xk3.707@dukeread07...
I feel like such a turdburglar! The part about the 15th is a little off,
too. So BOTH dates have to be formulas.

The paydays are supposed to be:
1) a) The 15th of every month, or, if it falls on a weekend, b) the
preceding weekday before the 15th
2) The last weekday of the month

"DOR" wrote in message
ups.com...
Try this - in A1 put your first legitimate date, such as 12/30/05, then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

Biff,

(I posted this yesterday on Excel Forum, but it doesn't seem to have
come across to Google ... so this may be a duplicate)

The possibility of the last weekday of the month, or by the 15th, being
on a holiday also occurred to me, as I looked at your neat use of
EOMONTH and WORKDAY, but I didn't follow up. Your suggestion to use the
Holidays parameter is excellent, but should possibly go a little
further. You need to modify the second argument of the WORKDAY function
to add not 5, but the number of workdays that exist between EOM (or
15th) and EOM-6 by using the NETWORKDAYS function (6 rather than 7
because the NETWORKDAYS function is inclusive), in addition to using
the Holidays argument (in both the NETWORKDAYS and WORKDAY functions).
This produces a very cumbersome formula as follows:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,NETWORKDAYS(EOMONTH(A1,0)-6,EOMONTH(A1,0),Holidays),Holidays),WORKDAY(DATE(Y
EAR(A1),MONTH(A1)+1,15)-7,NETWORKDAYS(DATE(YEAR(A1),MONTH(A1)+1,15)-6,DATE(YEAR(A1),MONTH(A1)+1,15),Holidays),Holidays
))


However, it appears to work. Nevertheless, I'd prefer to break it up
into its constituent parts. What a nightmare for others to decode!

No doubt, you or someone else will provide a more elegant solution. I
still like your neat use of EOM-7+5 workdays ...

DOR

Biff wrote:
You still need to enter the first date in A1:

Enter in B1:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,5),WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,15)-7,5))

Copy across, format as DATE, requires the ATP be installed.

Now, let me throw a monkey wrench into the mix that you may not have
considered!

What about paydays falling on holidays?

Luckily, the WORKDAY function can account for this. It can accept a third
argument that is a list of holiday dates.

You would create a list of the holiday dates and include a reference to that
list as a third argument:

WORKDAY(arg_1,arg_2,H1:H10)

Biff

"Yeah" wrote in message
news:ppKGg.1078$xk3.707@dukeread07...
I feel like such a turdburglar! The part about the 15th is a little off,
too. So BOTH dates have to be formulas.

The paydays are supposed to be:
1) a) The 15th of every month, or, if it falls on a weekend, b) the
preceding weekday before the 15th
2) The last weekday of the month

"DOR" wrote in message
ups.com...
Try this - in A1 put your first legitimate date, such as 12/30/05, then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays. However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way to
tell
Excel to output the date in the above manner?

(looking at that humongoid formula:) Holy ****!!!


"DOR" wrote in message
ups.com...
Biff,

(I posted this yesterday on Excel Forum, but it doesn't seem to have
come across to Google ... so this may be a duplicate)

The possibility of the last weekday of the month, or by the 15th, being
on a holiday also occurred to me, as I looked at your neat use of
EOMONTH and WORKDAY, but I didn't follow up. Your suggestion to use the
Holidays parameter is excellent, but should possibly go a little
further. You need to modify the second argument of the WORKDAY function
to add not 5, but the number of workdays that exist between EOM (or
15th) and EOM-6 by using the NETWORKDAYS function (6 rather than 7
because the NETWORKDAYS function is inclusive), in addition to using
the Holidays argument (in both the NETWORKDAYS and WORKDAY functions).
This produces a very cumbersome formula as follows:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,NETWORKDAYS(EOMONTH(A1,0)-6,EOMONTH(A1,0),Holidays),Holidays),WORKDAY(DATE(Y
EAR(A1),MONTH(A1)+1,15)-7,NETWORKDAYS(DATE(YEAR(A1),MONTH(A1)+1,15)-6,DATE(YEAR(A1),MONTH(A1)+1,15),Holidays),Holidays
))


However, it appears to work. Nevertheless, I'd prefer to break it up
into its constituent parts. What a nightmare for others to decode!

No doubt, you or someone else will provide a more elegant solution. I
still like your neat use of EOM-7+5 workdays ...

DOR

Biff wrote:
You still need to enter the first date in A1:

Enter in B1:

=IF(DAY(A1)<=15,WORKDAY(EOMONTH(A1,0)-7,5),WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,15)-7,5))

Copy across, format as DATE, requires the ATP be installed.

Now, let me throw a monkey wrench into the mix that you may not have
considered!

What about paydays falling on holidays?

Luckily, the WORKDAY function can account for this. It can accept a third
argument that is a list of holiday dates.

You would create a list of the holiday dates and include a reference to
that
list as a third argument:

WORKDAY(arg_1,arg_2,H1:H10)

Biff

"Yeah" wrote in message
news:ppKGg.1078$xk3.707@dukeread07...
I feel like such a turdburglar! The part about the 15th is a little off,
too. So BOTH dates have to be formulas.

The paydays are supposed to be:
1) a) The 15th of every month, or, if it falls on a weekend, b) the
preceding weekday before the 15th
2) The last weekday of the month

"DOR" wrote in message
ups.com...
Try this - in A1 put your first legitimate date, such as 12/30/05,
then
in A2 enter:

=DATE(YEAR(A1),1+MONTH(A1),IF(DAY(A1)=15,0,15))-IF(DAY(A1)<15,0,MAX(WEEKDAY(DATE(YEAR(A1),1+MONTH (A1),0),2)-5,0))

and drag across. It's not very elegant, but it appears to work, and
it
crosses years ok.

HTH

Declan

Yeah wrote:
What a goofy subject, I know. For my monthly budget, I want to output
the
date in every column, two weeks apart. These represent paydays.
However,
paydays are on the 15th and the last weekday of every month.

(For example: 8/31, 9/15, 9/29, 10/15, 10/31...)

Obviously, these dates won't all be 14 days apart. Is there any way
to
tell
Excel to output the date in the above manner?