Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
every quarter
Hi
Try application.WorksheetFunction.WorkDay(range("A1"), 90) -- Regards Roger Govier "A.G.M ash" wrote in message ... Actually just realised my error the answer is simple. but now i have come across another prob. how do i say if 90 days after 18th october = saturday or sunday then do nothing till monday "A.G.M ash" wrote: Hi Funny question for you all, been tryin to think of an answer but had no look. how do you tell excel to Do x every quarter but the quarter start and date to do is user defined i.e. Do x if date now() = 18th and month = three months after october date and month will be looked up in different cells but i can sort that out I just cant think of a way of telling excel to count 3 months from x month many many many thanx in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
every quarter
Roger,
Workdayis not a Worksheetfunction, it is ATP. I don't think he want to add 90 workdays anyway, I think he needs Debug.Print Format(Application.Run("ATPVBAEN.XLA!WORKDAY", Range("A1").Value + 89, 1), "dd/mm/yy") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try application.WorksheetFunction.WorkDay(range("A1"), 90) -- Regards Roger Govier "A.G.M ash" wrote in message ... Actually just realised my error the answer is simple. but now i have come across another prob. how do i say if 90 days after 18th october = saturday or sunday then do nothing till monday "A.G.M ash" wrote: Hi Funny question for you all, been tryin to think of an answer but had no look. how do you tell excel to Do x every quarter but the quarter start and date to do is user defined i.e. Do x if date now() = 18th and month = three months after october date and month will be looked up in different cells but i can sort that out I just cant think of a way of telling excel to count 3 months from x month many many many thanx in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
every quarter
Hi Bob
Thanks for pointing out the error of my ways<bg I was using XL2007 In the immediate window, with 18/10/2006 in A1 ?application.WorksheetFunction.WorkDay(range("A1") ,90) returned 39134 which I should have converted to 21/02/2007 (or used Format as you did) and I would have seen I was wrong. I quite agree, it should have been ?application.WorksheetFunction.WorkDay(range("A1") +89,1) which returns 39098 (16/01/2007) I should also have remembered I was in 2007 at the time, where Analysis Toolpak is incorporated. For earlier versions it would need to be what you posted. -- Regards Roger Govier "Bob Phillips" wrote in message ... Roger, Workdayis not a Worksheetfunction, it is ATP. I don't think he want to add 90 workdays anyway, I think he needs Debug.Print Format(Application.Run("ATPVBAEN.XLA!WORKDAY", Range("A1").Value + 89, 1), "dd/mm/yy") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try application.WorksheetFunction.WorkDay(range("A1"), 90) -- Regards Roger Govier "A.G.M ash" wrote in message ... Actually just realised my error the answer is simple. but now i have come across another prob. how do i say if 90 days after 18th october = saturday or sunday then do nothing till monday "A.G.M ash" wrote: Hi Funny question for you all, been tryin to think of an answer but had no look. how do you tell excel to Do x every quarter but the quarter start and date to do is user defined i.e. Do x if date now() = 18th and month = three months after october date and month will be looked up in different cells but i can sort that out I just cant think of a way of telling excel to count 3 months from x month many many many thanx in advance |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
every quarter
I thought that might have been the case.
We are going to need to be so precise <g -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Thanks for pointing out the error of my ways<bg I was using XL2007 In the immediate window, with 18/10/2006 in A1 ?application.WorksheetFunction.WorkDay(range("A1") ,90) returned 39134 which I should have converted to 21/02/2007 (or used Format as you did) and I would have seen I was wrong. I quite agree, it should have been ?application.WorksheetFunction.WorkDay(range("A1") +89,1) which returns 39098 (16/01/2007) I should also have remembered I was in 2007 at the time, where Analysis Toolpak is incorporated. For earlier versions it would need to be what you posted. -- Regards Roger Govier "Bob Phillips" wrote in message ... Roger, Workdayis not a Worksheetfunction, it is ATP. I don't think he want to add 90 workdays anyway, I think he needs Debug.Print Format(Application.Run("ATPVBAEN.XLA!WORKDAY", Range("A1").Value + 89, 1), "dd/mm/yy") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try application.WorksheetFunction.WorkDay(range("A1"), 90) -- Regards Roger Govier "A.G.M ash" wrote in message ... Actually just realised my error the answer is simple. but now i have come across another prob. how do i say if 90 days after 18th october = saturday or sunday then do nothing till monday "A.G.M ash" wrote: Hi Funny question for you all, been tryin to think of an answer but had no look. how do you tell excel to Do x every quarter but the quarter start and date to do is user defined i.e. Do x if date now() = 18th and month = three months after october date and month will be looked up in different cells but i can sort that out I just cant think of a way of telling excel to count 3 months from x month many many many thanx in advance |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
every quarter
We are going to need to be so precise <g
I guess so, especially as more 2007 questions start cropping up. We both seem to be burning the midnight oil!! but as our tank here is running a bit low, I might get off to bed now to conserve supplies<bg -- Regards Roger Govier "Bob Phillips" wrote in message ... I thought that might have been the case. We are going to need to be so precise <g -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Bob Thanks for pointing out the error of my ways<bg I was using XL2007 In the immediate window, with 18/10/2006 in A1 ?application.WorksheetFunction.WorkDay(range("A1") ,90) returned 39134 which I should have converted to 21/02/2007 (or used Format as you did) and I would have seen I was wrong. I quite agree, it should have been ?application.WorksheetFunction.WorkDay(range("A1") +89,1) which returns 39098 (16/01/2007) I should also have remembered I was in 2007 at the time, where Analysis Toolpak is incorporated. For earlier versions it would need to be what you posted. -- Regards Roger Govier "Bob Phillips" wrote in message ... Roger, Workdayis not a Worksheetfunction, it is ATP. I don't think he want to add 90 workdays anyway, I think he needs Debug.Print Format(Application.Run("ATPVBAEN.XLA!WORKDAY", Range("A1").Value + 89, 1), "dd/mm/yy") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Roger Govier" wrote in message ... Hi Try application.WorksheetFunction.WorkDay(range("A1"), 90) -- Regards Roger Govier "A.G.M ash" wrote in message ... Actually just realised my error the answer is simple. but now i have come across another prob. how do i say if 90 days after 18th october = saturday or sunday then do nothing till monday "A.G.M ash" wrote: Hi Funny question for you all, been tryin to think of an answer but had no look. how do you tell excel to Do x every quarter but the quarter start and date to do is user defined i.e. Do x if date now() = 18th and month = three months after october date and month will be looked up in different cells but i can sort that out I just cant think of a way of telling excel to count 3 months from x month many many many thanx in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
First Day of Next Quarter | Excel Discussion (Misc queries) | |||
quarter | Excel Worksheet Functions | |||
every quarter | Excel Programming | |||
Quarter End Date | Excel Worksheet Functions | |||
First and Last Day of the Quarter | Excel Worksheet Functions |