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.

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

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.

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.

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.

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.

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.