# Formatting Text String with Percentages and Calculated Time Frame

#### jski21

##### Board Regular
Good day all,

I'm using the text string below to determine if a percentage (E27) falls between two percentages and if so, combine it with text. The text is a percentage (ChartData!C13) and a calculated time frame (ChartData!B14):

=IF(AND(\$E\$27>=0,\$E\$27<=0.25),"Monthly spend to reach "&TEXT(ChartData!C13,"0%")& " ("&DATEDIF(\$A\$2,ChartData!B14,"M")&" mnths)","-")

If E27 is between the percentages, the text message will be visible. If not, the cell will only display a dash. My conundrum is when I click in the cell with the formula, the entire formula appears. Here's the formatting on each of these cells:

E27 = Percentage
ChartDataC13! = Percentage
A2 = Date (mmmm, dd, yyyy)
ChartDataB14! = Date (*m/dd/yyyy)
Cell with this formula = Text

My assumption is the differing formats within the text string are conflicting and are gumming up the works. Any solutions or advice would be most helpful.

Cheers,

jski21

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Alex Blakenburg

##### Well-known Member
Change the Cell with the formula in it to General.
Then go into edit mode in the cell and just hit enter.
It should return the result of the formula.

Making a cell text will treat the formula as text instead of a formula.

#### jski21

##### Board Regular
Thanks gentlemen. Thought I went through that cycle but must have missed it. There are four of these formulas (to cover everything from 0% to 100%) and the last one now show a #NUM! error:

=IF(\$E\$27>0.8,"Monthly spend to reach "&TEXT(ChartData!C16,"0%")& " ("&DATEDIF(\$A\$2,ChartData!B17,"M")&" mnths)","-")

#### Alex Blakenburg

##### Well-known Member

The only thing that can cause that is if B17 is less than A2 ( and this includes if B17 is blank)

#### jski21

##### Board Regular
Got it. Thanks. I need to revisit the time frames on this; they're actually rolling 7 year time frames with 21 month increments for targets. Thanks for the help and direction. Very instructive!

#### Alex Blakenburg

##### Well-known Member
No problem, glad we could help.

Replies
3
Views
68
Replies
0
Views
42
Replies
3
Views
171
Replies
13
Views
234
Replies
1
Views
114

1,141,496
Messages
5,706,682
Members
421,464
Latest member
apicsofcake

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back