Putting Current Month in Text Statement

E

evandavies75

I create a monthly report for my client showing how many hours (and
the dollar value of those hours) have been used in that month as well
as where we stand year-to-date. There are 26 line items representing
separate projects in the body of the report. Based on the fee my
company is paid for each project, I have created two columns on the
right-hand-side of the report to show the percentage of total hours
the client has used to date and the remaining hours the client has
available for the remainder of the year.

This is all background to give the context of the document. My focus
is on two formulas I have created to appear beneath the body of the
report. One formula calculates and displays what I call "fee
performance y-t-d." The formula is: ="Fee Performance Y-T-D "&TEXT(I30/
C30*(MONTH(F3))),"0%"). Where I30 = total dollar amount of the fee
used to date for all projects; C30 = the total monthly fee payment for
all projects; F3 = the current month that's entered at the top of the
report.

Next to this I have another line of text that shows the fee
performance for just that month. The formula is "Fee Performance for
Month "&TEXT((G30/C30),"0%). So here is the crux of my question. I
want to customize this statement so on each monthly report the actual
month appears as text rather than the generic "fee performance for
month."

How can alter the formula so the statement reads, for example, "Fee
Performance for April 38%" ?
 
J

JE McGimpsey

How can alter the formula so the statement reads, for example, "Fee
Performance for April 38%" ?

One way:

="Fee Performance for "&TEXT(TODAY(),"MMMM")&TEXT((G30/C30)," 0%")
 
E

evandavies75

One way:

   ="Fee Performance for "&TEXT(TODAY(),"MMMM")&TEXT((G30/C30)," 0%")

Thank you for providing this answer. It works for my need to some
degree. What I failed to include in the description of my problem is
that I have a separate worksheet for each month. This formula is
based on the current date therefore I came up with a work-around so it
can adapted for the monthly worksheet/reports. Here is what I did
(using the February report as an example): ="Fee Performance for "&TEXT
(DATE(2009,2,1),"MMMM")&TEXT((G30/C30)," 0%").

This works fine except I'd like a formula that is dynamic so I don't
have to manually type in the the date criteria for each monthly
report.

What I am asking for assistance on should be considered a minor
refinement recognizing that there are users with problems much larger
than this. I suppose I am asking more out of curiosity than anything
else.
 
J

JE McGimpsey

Thank you for providing this answer. It works for my need to some
degree. What I failed to include in the description of my problem is
that I have a separate worksheet for each month. This formula is
based on the current date therefore I came up with a work-around so it
can adapted for the monthly worksheet/reports. Here is what I did
(using the February report as an example): ="Fee Performance for "&TEXT
(DATE(2009,2,1),"MMMM")&TEXT((G30/C30)," 0%").

This works fine except I'd like a formula that is dynamic so I don't
have to manually type in the the date criteria for each monthly
report.

What I am asking for assistance on should be considered a minor
refinement recognizing that there are users with problems much larger
than this. I suppose I am asking more out of curiosity than anything
else.

Well, the "minor" refinement depends on how you expect XL to figure out
what month the sheet represents...

If you have a date in a cell on that sheet (say, A1), then

="Fee Performance for "&TEXT(A1,"MMMM")&TEXT((G30/C30)," 0%")

is an easy solution.

Otherwise, you'll need to specify how XL is supposed to know...
 
E

evandavies75

Well, the "minor" refinement depends on how you expect XL to figure out
what month the sheet represents...

If you have a date in a cell on that sheet (say, A1), then

   ="Fee Performance for "&TEXT(A1,"MMMM")&TEXT((G30/C30)," 0%")

is an easy solution.

Otherwise, you'll need to specify how XL is supposed to know...

This worked perfectly for my needs. Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top