Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




MidMonth function help
With other's help, I was provided a function to assist in determining
the working midmonth 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? 
#2




Answer: MidMonth function help
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
Formula:
I hope that helps!
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.programming




MidMonth function help
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 midmonth 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 
#4
Posted to microsoft.public.excel.programming




MidMonth function help
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 midmonth 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 midmonth date. Ex  04/24/2012 would return 05/15/2012. Thanks for your help with this Claus! 
#5
Posted to microsoft.public.excel.programming




MidMonth function help
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 midmonth 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) 
#6
Posted to microsoft.public.excel.programming




MidMonth function help
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 midmonth 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!! 
#7
Posted to microsoft.public.excel.programming




MidMonth function help
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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
function to fill all days of month to end of month  Excel Worksheet Functions  
function to fill all days of month to end of month  Excel Worksheet Functions  
Function or formula to convert "text" month to number of month  Excel Discussion (Misc queries)  
When using MONTH function on Blank Cell!! Returns Month=Jan!  Excel Discussion (Misc queries)  
=Month function in Excel gives incorrect month  New Users to Excel 