VBA date in function
 

Hello,
I've been reading the posting and have found them to be very
informative in learning VBA. However, i have not been able to find
this:

i currently have a commission function that works great, but i can
only figure out how to get a quarterly number from it when i change
the date manually.

for example:
This is for Q3 '04
Function fee(Assets)
' schedule investment management fee
Const tier1 = 0.01 * (92 / 365)
Const tier2 = 0.01 * (92 / 365)
Const tier3 = 0.01 * (92 / 365)
Const tier4 = 0.01 * (92 / 365)
Const tier5 = 0.01 * (92 / 365)
' calculates annual management fee
Select Case Assets
Case 1 To 499999.99
fee = Assets * tier1
Case 500000 To 999999.99
fee = 500000 * tier1 + (Assets - 500000) * tier2
Case 1000000 To 1999999.99
fee = 500000 * tier1 + 500000 * tier2 + (Assets - 1000000)
* tier3
Case 2000000 To 5000000
fee = 500000 * tier1 + 500000 * tier2 + 1000000 * tier3 + _
(Assets - 2000000) * tier4
Case Is = 5000000
fee = 500000 * tier1 + 500000 * tier2 + 1000000 * tier3 + _
3000000 * tier4 + (Assets - 5000000) * tier5
End Select
End Function

how would i make the date 92 update to 91 in the next quarter Q1 '04
(90 days)(based on excels date format) and the future q1,q2,q3,q4?
But still have the history
Can this happen or is it a pipe dream?
thanks,
RTB

VBA date in function
 

What makes this quarter 92? And how does quarter after this (Q3 04) be Q1
04?


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RTB" wrote in message
om...
Hello,
I've been reading the posting and have found them to be very
informative in learning VBA. However, i have not been able to find
this:

i currently have a commission function that works great, but i can
only figure out how to get a quarterly number from it when i change
the date manually.

for example:
This is for Q3 '04
Function fee(Assets)
' schedule investment management fee
Const tier1 = 0.01 * (92 / 365)
Const tier2 = 0.01 * (92 / 365)
Const tier3 = 0.01 * (92 / 365)
Const tier4 = 0.01 * (92 / 365)
Const tier5 = 0.01 * (92 / 365)
' calculates annual management fee
Select Case Assets
Case 1 To 499999.99
fee = Assets * tier1
Case 500000 To 999999.99
fee = 500000 * tier1 + (Assets - 500000) * tier2
Case 1000000 To 1999999.99
fee = 500000 * tier1 + 500000 * tier2 + (Assets - 1000000)
* tier3
Case 2000000 To 5000000
fee = 500000 * tier1 + 500000 * tier2 + 1000000 * tier3 + _
(Assets - 2000000) * tier4
Case Is = 5000000
fee = 500000 * tier1 + 500000 * tier2 + 1000000 * tier3 + _
3000000 * tier4 + (Assets - 5000000) * tier5
End Select
End Function

how would i make the date 92 update to 91 in the next quarter Q1 '04
(90 days)(based on excels date format) and the future q1,q2,q3,q4?
But still have the history
Can this happen or is it a pipe dream?
thanks,
RTB

VBA date in function
 

Bob,
thanks for replying. I was expecting to see a reply on the google group
but i guess this group sees it as well.(i'm new to the newsgroup thing)

Q3 '04 has 92 days and Q1 '04 has 90 days.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

VBA date in function
 

Robert,

Does this help? It is a function that returns the number of days

Function DaysInQtr(Quarter As Long, qYear As Long)
Dim StartMonth As Long
Dim EndMonth As Long
Dim DaysBetween As Long

StartMonth = (Quarter - 1) * 3 + 1
EndMonth = Quarter * 3
DaysInQtr = DateSerial(qYear, EndMonth + 1, 0) - DateSerial(qYear,
StartMonth, 1) + 1

End Function


You invoke it with Msgbox DaysInQtr(3,2004) as an example.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"robert burger" wrote in message
...

Bob,
thanks for replying. I was expecting to see a reply on the google group
but i guess this group sees it as well.(i'm new to the newsgroup thing)

Q3 '04 has 92 days and Q1 '04 has 90 days.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

VBA date in function
 

Bob,
thank you! the date function you gave me works on its own. However, i
am still confused as to how i would make the value populate within my
current code from entering it in a msgbox from the sheet and therefore
giving the right result in the sheet depending on the dollar amount,
rate, and "date". Anyways, i have bought the Excel 2000 power
Programming w/ VBA book by J. Walkenbach and am making my way through
it, i had now idea this stuff was so powerful. The book is a little
advanced but i think with the help of newsgroups like this and others i
will do ok.
thanks,
RTB



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!