converting "May" to 31, "June" to 30, etc.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=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)
 
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.
 
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 said:
=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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
=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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
=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)



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
Click to expand...
Click to expand...
Click to expand...
 
I tried to <bg>

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



Dave F said:
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 said:
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 said:
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.


:

=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)



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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top