Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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! |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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) |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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!! |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#7
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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 |