Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to lookup date on tab in excel and populate date on calen | Excel Worksheet Functions | |||
Difference betwen Excel Date () Function and System Date | Excel Worksheet Functions | |||
Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions | |||
A VBA function to give serial date for date and time? | Excel Programming |