With other's help, I was provided a function to assist in determining
the working mid-month day of the month (or the next month), but it
needs a little refining... Let me explain. We'll start with the
function:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)=15),16) ,-1)

If the date in cell a1 is 9/1/2012, then the function returns
9/14/2012, which is the last working day on or before the 15th of that
month. Put in 09/17/2012 and it returns 10/15/2012, which is correct.
But, if the date is 08/15/12 then the function returns 09/14/2012,
which is incorrect. It should still return 08/15/2012 since this is
the working day on or before the 15th. I can correct that by removing
the =, but then 09/15/2012 would return 09/14/2012 instead of
10/15/12. Can someone help me w/ this?

Sure, I can help you with that!

The issue with the current formula is that it adds one month to the original date if the day is greater than or equal to 15. This works for most cases, but not for dates like 08/15/12 where the day is exactly 15.

To fix this, we can add an additional

1. IF statement to check if the day is exactly 15.
2. If it is, then we want to use the original date instead of adding a month.

Here's the updated formula:

This formula first checks if the day in A1 is 15. If it is, then it returns A1 (the original date). If it's not, then it uses the original formula to find the last working day on or before the 16th of the next month.

I hope that helps!

Hi Matthew,

Am Mon, 17 Sep 2012 10:59:01 -0700 (PDT) schrieb Matthew Dyer:

With other's help, I was provided a function to assist in determining
the working mid-month day of the month (or the next month), but it
needs a little refining... Let me explain. We'll start with the
function:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)=15),16) ,-1)

If the date in cell a1 is 9/1/2012, then the function returns
9/14/2012, which is the last working day on or before the 15th of that
month. Put in 09/17/2012 and it returns 10/15/2012, which is correct.
But, if the date is 08/15/12 then the function returns 09/14/2012,
which is incorrect. It should still return 08/15/2012 since this is
the working day on or before the 15th. I can correct that by removing
the =, but then 09/15/2012 would return 09/14/2012 instead of
10/15/12. Can someone help me w/ this?

try:
=IF(WORKDAY(DATE(YEAR(A1),MONTH(A1),16),-1)<A1,WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,16),-1),WORKDAY(DATE(YEAR(A1),MONTH(A1),16),-1))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sep 17, 12:29*pm, Claus Busch wrote:
Hi Matthew,

Am Mon, 17 Sep 2012 10:59:01 -0700 (PDT) schrieb Matthew Dyer:

With other's help, I was provided a function to assist in determining
the working mid-month day of the month (or the next month), but it
needs a little refining... Let me explain. We'll start with the
function:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)=15),16) ,-1)

If the date in cell a1 is 9/1/2012, then the function returns
9/14/2012, which is the last working day on or before the 15th of that
month. Put in 09/17/2012 and it returns 10/15/2012, which is correct.
But, if the date is 08/15/12 then the function returns 09/14/2012,
which is incorrect. It should still return 08/15/2012 since this is
the working day on or before the 15th. I can correct that by removing
the =, but then 09/15/2012 would return 09/14/2012 instead of
10/15/12. Can someone help me w/ this?

try:
=IF(WORKDAY(DATE(YEAR(A1),MONTH(A1),16),-1)<A1,WORKDAY(DATE(YEAR(A1),MONTH(*A1)+1,16),-1),WORKDAY(DATE(YEAR(A1),MONTH(A1),16),-1))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

This works very well, unless you input a date beyond the mid month
date. Then it returns an error #name. I would like it to be able to
return the following month's mid-month date. Ex - 04/24/2012 would
return 05/15/2012. Thanks for your help with this Claus!

On Mon, 17 Sep 2012 10:59:01 -0700 (PDT), Matthew Dyer wrote:

With other's help, I was provided a function to assist in determining
the working mid-month day of the month (or the next month), but it
needs a little refining... Let me explain. We'll start with the
function:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)=15),16 ),-1)

If the date in cell a1 is 9/1/2012, then the function returns
9/14/2012, which is the last working day on or before the 15th of that
month. Put in 09/17/2012 and it returns 10/15/2012, which is correct.
But, if the date is 08/15/12 then the function returns 09/14/2012,
which is incorrect. It should still return 08/15/2012 since this is
the working day on or before the 15th. I can correct that by removing
the =, but then 09/15/2012 would return 09/14/2012 instead of
10/15/12. Can someone help me w/ this?

Try this:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(WORKDAY(
DATE(YEAR(A1),MONTH(A1),DAY(A1)-1),1))15),16),-1)

On Sep 17, 12:55*pm, Ron Rosenfeld wrote:
On Mon, 17 Sep 2012 10:59:01 -0700 (PDT), Matthew Dyer wrote:
With other's help, I was provided a function to assist in determining
the working mid-month day of the month (or the next month), but it
needs a little refining... Let me explain. We'll start with the
function:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(A1)=15),16 ),-1)

If the date in cell a1 is 9/1/2012, then the function returns
9/14/2012, which is the last working day on or before the 15th of that
month. Put in 09/17/2012 and it returns 10/15/2012, which is correct.
But, if the date is 08/15/12 then the function returns 09/14/2012,
which is incorrect. It should still return 08/15/2012 since this is
the working day on or before the 15th. I can correct that by removing
the =, but then 09/15/2012 would return 09/14/2012 instead of
10/15/12. Can someone help me w/ this?

Try this:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(WORKDAY(
DATE(YEAR(A1),MONTH(A1),DAY(A1)-1),1))15),16),-1)- Hide quoted text -

- Show quoted text -

That does the trick! thanks!!

On Mon, 17 Sep 2012 13:14:07 -0700 (PDT), Matthew Dyer wrote:

Try this:

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+(DAY(WORKDAY(
DATE(YEAR(A1),MONTH(A1),DAY(A1)-1),1))15),16),-1)- Hide quoted text -

- Show quoted text -

That does the trick! thanks!!

Glad to help. Thanks for the feedback.