Help! ISO formula for inconsistent monthly dates

[Yeah]

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]

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]

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]

I used 3 cells:
A1, B1 and C1.

What did you use?

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

 

[DOR]

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

 

[Biff]

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

 

[Biff]

It also gave me a Circular Reference

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

Biff

 

[Dave Peterson]

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

It also gave me a Circular Reference

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

Biff

 

[Yeah]

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

 

[Biff]

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

 

[DOR]

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

 

[Yeah]

(looking at that humongoid formula Holy shit!!!