| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
converting "May" to 31, "June" to 30, etc.
I'm using the following formula to calculate cost of funds:
=(SUM($E4:I4)-SUM($V4:$W4))*$AJ$2*(AN$3/360). $E4:I4 is capital, $V4:$W4 is depreciation, $A$J2 is the cost of funds rate, and AN$3 is the number of days in the month. In this case, AN$3 = 31. But I want AN$3 to read "May". How would I modify the formula to convert "May" into 31, "June" into 30, etc. Algebraically, the math is: Cost of funds = [Net book value]*[COF rate]*{[days in month]/360} Thanks. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
Hi, Dave-
Is AN3 a date cell? This formula finds the last day in a month specified in a date cell: =EOMONTH(AN3,0) Can you work that in somehow? Dave O |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
=DAY(MIN(DATE(YEAR(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY()))),MONTH(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))+{2,1},DAY(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))*{0,1}))-1)
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... I'm using the following formula to calculate cost of funds: =(SUM($E4:I4)-SUM($V4:$W4))*$AJ$2*(AN$3/360). $E4:I4 is capital, $V4:$W4 is depreciation, $A$J2 is the cost of funds rate, and AN$3 is the number of days in the month. In this case, AN$3 = 31. But I want AN$3 to read "May". How would I modify the formula to convert "May" into 31, "June" into 30, etc. Algebraically, the math is: Cost of funds = [Net book value]*[COF rate]*{[days in month]/360} Thanks. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
Argh- sorry, Dave, need to amend that a touch:
=DAY(EOMONTH(AN3,0) ) But I also have a question: if you're using 360 days as the total number of days in a year, will you be diluting the accuracy of your formula if the total days in the year exceed 360? Seems like a bit of a mismatch. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
Wow that's a crazy formula.
I think the easier answer is just to have, for example, "May" appear as 5/31/2007. Then =DAYS(AN3) yields 31, etc. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Bob Phillips" wrote: =DAY(MIN(DATE(YEAR(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY()))),MONTH(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))+{2,1},DAY(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))*{0,1}))-1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... I'm using the following formula to calculate cost of funds: =(SUM($E4:I4)-SUM($V4:$W4))*$AJ$2*(AN$3/360). $E4:I4 is capital, $V4:$W4 is depreciation, $A$J2 is the cost of funds rate, and AN$3 is the number of days in the month. In this case, AN$3 = 31. But I want AN$3 to read "May". How would I modify the formula to convert "May" into 31, "June" into 30, etc. Algebraically, the math is: Cost of funds = [Net book value]*[COF rate]*{[days in month]/360} Thanks. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
Yeah but then you have to remember 30 days in Sep, 28 in Feb, except a leap
year. The formula handles it all. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... Wow that's a crazy formula. I think the easier answer is just to have, for example, "May" appear as 5/31/2007. Then =DAYS(AN3) yields 31, etc. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Bob Phillips" wrote: =DAY(MIN(DATE(YEAR(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY()))),MONTH(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))+{2,1},DAY(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))*{0,1}))-1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... I'm using the following formula to calculate cost of funds: =(SUM($E4:I4)-SUM($V4:$W4))*$AJ$2*(AN$3/360). $E4:I4 is capital, $V4:$W4 is depreciation, $A$J2 is the cost of funds rate, and AN$3 is the number of days in the month. In this case, AN$3 = 31. But I want AN$3 to read "May". How would I modify the formula to convert "May" into 31, "June" into 30, etc. Algebraically, the math is: Cost of funds = [Net book value]*[COF rate]*{[days in month]/360} Thanks. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
converting "May" to 31, "June" to 30, etc.
Agreed. However the person who will ultimately use this workbook has asked
that formulas be kept as simple as possible. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Bob Phillips" wrote: Yeah but then you have to remember 30 days in Sep, 28 in Feb, except a leap year. The formula handles it all. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... Wow that's a crazy formula. I think the easier answer is just to have, for example, "May" appear as 5/31/2007. Then =DAYS(AN3) yields 31, etc. -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "Bob Phillips" wrote: =DAY(MIN(DATE(YEAR(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY()))),MONTH(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))+{2,1},DAY(DATEVALUE("01-"&AN3&"-"&YEAR(TODAY())))*{0,1}))-1) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Dave F" wrote in message ... I'm using the following formula to calculate cost of funds: =(SUM($E4:I4)-SUM($V4:$W4))*$AJ$2*(AN$3/360). $E4:I4 is capital, $V4:$W4 is depreciation, $A$J2 is the cost of funds rate, and AN$3 is the number of days in the month. In this case, AN$3 = 31. But I want AN$3 to read "May". How would I modify the formula to convert "May" into 31, "June" into 30, etc. Algebraically, the math is: Cost of funds = [Net book value]*[COF rate]*{[days in month]/360} Thanks. Dave -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| create links to check boxes marked "good" fair"and "bad" | Excel Worksheet Functions | |||
| Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
| Insert "-" in text "1234567890" to have a output like this"123-456-7890" | Excel Discussion (Misc queries) | |||
Linear Mode
