How do I calculate escalation rates using dates and a set value
If I have a projected that needs to be escalated using start and completion
dates by a certain percentage per year, how do I write the formula. For
example if my project starts May 26, 2010 and lasts until may 26 2014 I want
to add escalation to midpoint at 4% per year.
...formula help #8
The following formula queries and totals information on a specified sheet.
In this case the specified sheet name is 2003
I have several sheets (2001, 2002, 2003, 2004.....)
I would like to have the value for the sheet name in the formula reference a
cell in which I could type in the name of the sheet I want to query.
Can someone tell me how to accomplish this. Just changing the '2003' in the
formula to the reference cell doesn't work. (FYI - the reference cell will
=SUMIF((INDIRECT("'...DTS Export fails on bad date
While trying to import an excel spreadsheet to a SQL Server table, DTS
fails, pointing to a column with date values. I have looked at all the date
values, and they appear correct. I need to get this excel spreadsheet
loaded to a database table. Is there any way to pinpoint the exact cell
causing the problem? Or, can you think of another way to export the data in
the spreadsheet besides DTS that might not be so sensitive to data content?
...Trouble with time formats
Any help appreciated!
I need to work out the average speed it would take to cover a certain
If you covered 10 miles in 28 minutes what would teh average speed be...
Column A: 10 miles
Column B: 28 minutes
Column C: Would return 21.43mph.
I then want to to add time increments of say 15 seconds in rows so that I
can see that if it took 28 minutes, 45 seconds, the average speed column
would return 20.87mph.
I can work it out by entering 28.75 for the time (or 70.50 for 1h,10m,30s),
but I want to format it as time, i.e: 00:28:45 (or 01:10:30)
Is this possible?
Thanks...Copying sorksheet formatting
How do I copy worksheet formatting, inc. custom headers,
footers, column & row formatting across all the worksheets
in a workbook?
one way: formating them all at the same time:
- group the sheets (hold down the SHIFT key while selecting the sheets)
- apply the format to a cell, column, row
> How do I copy worksheet formatting, inc. custom headers,
> footers, column & row formatting across all the worksheets
> in a workbook?
You can also select the entire sheet with CTRL+A and do Edit / Copy, then group
yo...disc date for next month
We want to be able to choose a discount date for the next month the discount
is allowed. Our terms are 2% 10th net 15th. We can choose the due date for
the 15th of the next month, but we can't choose the discount date as the 10th
of the next month. All the invoices entered into the system from the 1st thru
the 10th calculate the discount for the current month instead of the next
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the me...combine text & date formula to show mmmm dd, yyyy?
How do I combine text and date formula to show the written date and not the
Excel number date? I have text in a couple of cells and a date formula in
another cell(input from a different worksheet). I have the formula =A3 & F3
& A4 in A1 and would like it to read: We went to the station on December 21,
2009 and took the train. However, it is reading We went to the station on
40168 and took the train.
Try something like this...
A1 = Today is
B1 = 12/21/2009
=A1&" "&TEXT(B1,"mmmm dd, yyyy")
Returns: Today is December 21, 2009
...Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two
conditions for green, without using two conditions for green is there
a way to use OR somehow to beat the limit?
PS Win2000 + XL2002
Sure ... just click on "Formula Is", and you can use "OR" for more then two
Will trigger the set format if "any" of the above equate to "True".
Please keep all corr...Is there a way to copy and paste the date into worksheets
Is there a way to copy and paste dates into the worksheet withou
individually typing up each one
mark_vi_'s Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2450
View this thread: http://www.excelforum.com/showthread.php?threadid=38137
Is there a way to insert it the date into the the worksheet tab (not th
cells in the actual worksheet but the name of the worksheet itself
mark_vi_'s Profile: ht...Excel's column width format box
Does anyone know why I am unable to enter a number into Excel's column width
box, yet using the mouse, I am able to adjust the width. When opening the
text box, I can delete the number that appears, but am unable to even reenter
that one after it is deleted. I must tell you that I'm a new user who's
working through numerous tutorials.
do you get an error message or what happens exactly
> Does anyone know why I am unable to enter a number into Excel's
> column width box, yet using the mouse, I am ...Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are
less than 02/05/10 and the name begins with FHLM, heres what i came up
I keep gettin a bunch of ###### or 0, depending on how I move the data
"Senor Martinez" wrote:
> IM trying to create a formula that gives me...Help! Search & Replacing time formats
I have a whole spreadsheet full of fields similiar to the following
10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM
12:20PM 2:25PM 4:30PM 7:15PM
10:00AM 12:30PM 2:45PM 9:15PM
10:40AM 1:10PM 5:00PM 7:20PM 9:35PM
I need to convert all times to 24 hour, and drop the AM & PM.
The later is easy, but how do I do a quick Search & Replace without
incorrectly converting 10:**AM to 22:** etc.
Any tips would be appreciated.
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly fro...Excel 2003 not recognizing whole dollar amounts, formats amt as ce
In my earlier version of Excel, (Office 97), I could format the cells to
automatically enter amounts as dollars and cents. For example, I would type
30. and the amount was shown as $30.00. Or, I could type 4357, and the
amount was shown as $43.57. In the Office 2003 version if I type 30., the
amount is reflected in the cell as 30 cents instead of $30.00.
Yes that is a change that other people have complained about when one uses
(No private emails please)
"hat1" <email@example.com> wrote in message
news:2C3CBB7C...Formatting data table
I have a data table shown under a diagram. However it won't display all the
decimals. I.e. in the input to the diagram for january 2006 is 1.123, but
only 1.1 is shown in the data table. The only solution seems to be to enlarge
the diagram but this is not an option in this particular case!
As a last resort I could embed a table under a traditional diagram without
the data table, but that is second best.
Looking forward to any help
If you right click on the data table you can change the font size by clicking
on 'format data table' and the font tab. You can type in the size ...Grouping dates in pivot table
I am pulling data from a SQL Server database to create a pivot table.
Excel (07) is not recognizing the field as a date. This is a field I
would like to group by in the pivottable. I am aware of all the
techniques to convert this to a date field, but I am searching for an
answer as to why XL pivot tables cannot consume the dates directly
from a sql query.
I have played with bringing the dates back in a number of differenent
formats with no success.
Any insights into this would be appreciated.
Probably coming in as text and yuo may only need to copy an unused cell and
paste sp...how do i change dates on my calendar and keep my pics
Publisher doesn't support this. You would need to do it manually.
Microsoft Publisher MVP
~pay it forward~
This posting is provided "AS IS" with no warranties, and
confers no rights.
"Bob T" <Bob T@discussions.microsoft.com> wrote in message
Is it possible to shade fields progressively darker or lighter depending on
the values. For example, I would like the following values to have the
-5 dark red
-4 lighter red
-3 slightly lighter red
-2 etc, etc
1 light green
5 dark green
One step further - would it be possible to tell excel to work out which is
the lowest and highest number in the data set and work out for itself how
light or dark to shade the values e.g, if my dataset happened to have the
values -50, -40, etc excel would work out which was a low value and which was
a high one.
Conditional formatting ...Conditional Format Help Needed
I would like to apply a Conditional Format based on two factors. Currently I
have two different Conditions set up.
=IF(IF($G2="Ready to Publish",1,0)=1,1,0)
This format successfully works to gray out and cross out text that matches
"Ready to Publish."
This format applies alternating row fills for easier viewing.
While both of these formulas work, my problem is Condition 1 overrides
Condition 2 when "Ready to Publish" is detected. Is there a way to combine
the two formulas so they wouldn't overr...Formatting numbers
Pub2002/XP - how to format numbers in a table - I want
it to automatically space for thousands etc and align the
numbers correctly ie.
1 650 243 500 etc
You will have to do it manually, or copy and paste it from a spreadsheet.
See if Courier font will do it. Most other fonts do proportional spacing
i.e. the letter m is wider than an l. In Courier (maybe teletype,
typewriter and a few other fonts) use the same width of space and thus will
line up columns from row to row.
Vancouver, USA, a great city in one of the 45+ countries in America!
"simonh" <si...Looking up a date
Trying to get this to work, but no success:
The value in A6 is a function; not hard-coded.
It seems to work with one date and one result vector, but not two, and
ultimately I will need several, maybe 7 or 8.
You can't use functions in array constants.
>ultimately I will need several, maybe 7 or 8.
Why don't you just create a table in ascending order by date:
.............A.......Custom Format for WEEKNUM(Today())
I am using WEEKNUM in Excel to do some COUNTIF functions and have problems
with matches because Excel returns a single digit rather than 2 of weeks 1
throuhg 9 of any given year. Using teh COUNTIF function 20051 then
mathmatically is the same as 200510. Is there some way to format the week
returned to be 2 digits?
Something like this:
"RonB" <firstname.lastname@example.org> wrote in message
> I am using WEEKNUM in Excel to do some COUNTIF func...Conditional Format Q
I wish to create a conditional format formula that will change the
background colour in a cell to Blue, if the value of another cell is
between to values (which are also detailed)
Cell I want to change is D8 on Sheet1
The dependent cell of D8 is in A8 on Sheet1
The two values which A8 must be between/or equal to is in F7 & H7 on
I just can't get my head around how to construct this
Select cell D8.
Format/ Conditional Formatting/ Formula Is/
=AND(A8>=Sheet2!F7,A8<=Sheet2!H7) if F7 is less than or equal to H7
Format/ Conditional Formatting/ For...can add multiple analysis code in a single FRx Row format
Currently we can add several analysis code in FRx Row format by using several
rows (each row will contain only 1 analysis code - limitation of FRx with
MDA). The client has many analysis codes in each analysis group. What they
need is to add some of the analysis code in a single row; separating it into
several rows will add more work in designing the FRx report.
There should be a way for next releases of FRx to handle adding mulitple
analysis codes in a single row or column of the report.
...edit hyperlink code cont from 12/8/04
I tried running this without success. Can anybody tell me why?
Dim OldStr As String, NewStr As String
OldStr = "c:\My Templates\Profile Database\"
NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
Dim hyp As Hyperlink
For Each hyp In ActiveSheet.Hyperlinks
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>> > "Ron de Bruin" wrote:
> >> I copy this from David McRitchie his site
> >> http://www.mvps.org/dmcritchie/excel/buildtoc...Erratic Report Field Format
Having strange problem with erratic behavior of field format on a
report/subreport, which I hope someone can solve. (Using Ac2003)
On the subreport Detail, I have a field "Charges", Where Format=
Fixed, Decimal Places = 2, and ControlSource =
On the subreport Footer, I have a field "SumCharges", whose
Format=Currency, Decimal places = 2, and
ControlSource==DSum("[ChargesApproved]","TopN") (where TopN is the
underlying query for the subreport).
Then, in the Report Detail section, there is a fie...