Mid-Month function help

[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?

 

[Claus Busch]

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

 

[Matthew Dyer]

Hi Matthew,

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


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

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!

 

[Ron Rosenfeld]

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)

 

[Matthew Dyer]

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

 

[Ron Rosenfeld]

That does the trick! thanks!!

Glad to help. Thanks for the feedback.