Excel Question #21

```Hello All,

How can I subtract dates to get whole numbers in Excel? I am trying to link
DOB's with a chart that rates based on age.

example:  12/20/2005 - 12/20/1955 = 50 years old.

Any and all assistance is greatly appreciated.

Thanks.

```
 0
AB (54)
12/20/2005 7:31:07 PM
excel.misc 78881 articles. 5 followers.

11 Replies
390 Views

Similar Articles

[PageSpeed] 27

```Use the DATEDIF function.

=DATEDIF(start_date,end_date,"Y")

www.cpearson.com/excel/datedif.htm .

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"AB" <AB@discussions.microsoft.com> wrote in message
> Hello All,
>
> How can I subtract dates to get whole numbers in Excel? I am
> DOB's with a chart that rates based on age.
>
> example:  12/20/2005 - 12/20/1955 = 50 years old.
>
> Any and all assistance is greatly appreciated.
>
> Thanks.
>

```
 0
chip1 (1821)
12/20/2005 7:36:50 PM
```A1: 12/20/2005
A2: 12/20/1955
formual
=year(a1)-year(a2)

gives you 50.

"AB" wrote:

> Hello All,
>
> How can I subtract dates to get whole numbers in Excel? I am trying to link
> DOB's with a chart that rates based on age.
>
> example:  12/20/2005 - 12/20/1955 = 50 years old.
>
> Any and all assistance is greatly appreciated.
>
> Thanks.
>
```
 0
Nikki (101)
12/20/2005 7:42:03 PM
```Chip,

Thanks for your response.  I tried that but it did not do what I wanted it
to.  What I am trying to do is the following.  I have a list of about 600
employees.  I am trying to put together a formular where I can tell Excel to
calculate the birthdate and then based on its results do a VLookup to see
what the coresponding rate is and then have it pull that rate into a
specified cell.  For example, is Bob Smith was born on 12/20/1950, I need
exce to recognize that he is 50 years of age.  Then look at what rate
corresponds to 50 years of age and then put it in Cell A4.

Please let me know if this makes sense or if you need additional info.

Thanks.

Chip Pearson" wrote:

> Use the DATEDIF function.
>
> =DATEDIF(start_date,end_date,"Y")
>
> www.cpearson.com/excel/datedif.htm .
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "AB" <AB@discussions.microsoft.com> wrote in message
> > Hello All,
> >
> > How can I subtract dates to get whole numbers in Excel? I am
> > DOB's with a chart that rates based on age.
> >
> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >
> > Any and all assistance is greatly appreciated.
> >
> > Thanks.
> >
>
>
>
```
 0
AB (54)
12/20/2005 7:48:03 PM
```I guess I don't really understand your problem. If you have a
birthdate, the formula

=DATEDIF(birth_date,TODAY(),"Y")

will return the age in years.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"AB" <AB@discussions.microsoft.com> wrote in message
news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
> Chip,
>
> Thanks for your response.  I tried that but it did not do what
> I wanted it
> to.  What I am trying to do is the following.  I have a list of
> employees.  I am trying to put together a formular where I can
> tell Excel to
> calculate the birthdate and then based on its results do a
> VLookup to see
> what the coresponding rate is and then have it pull that rate
> into a
> specified cell.  For example, is Bob Smith was born on
> 12/20/1950, I need
> exce to recognize that he is 50 years of age.  Then look at
> what rate
> corresponds to 50 years of age and then put it in Cell A4.
>
> Please let me know if this makes sense or if you need
>
> Thanks.
>
>
>
>
> Chip Pearson" wrote:
>
>> Use the DATEDIF function.
>>
>> =DATEDIF(start_date,end_date,"Y")
>>
>> www.cpearson.com/excel/datedif.htm .
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "AB" <AB@discussions.microsoft.com> wrote in message
>> > Hello All,
>> >
>> > How can I subtract dates to get whole numbers in Excel? I am
>> > DOB's with a chart that rates based on age.
>> >
>> > example:  12/20/2005 - 12/20/1955 = 50 years old.
>> >
>> > Any and all assistance is greatly appreciated.
>> >
>> > Thanks.
>> >
>>
>>
>>

```
 0
chip1 (1821)
12/20/2005 7:53:50 PM
```Nikki,

Thanks.  I tried that but it gave me 02/19/1900.

"Nikki" wrote:

> A1: 12/20/2005
> A2: 12/20/1955
> formual
> =year(a1)-year(a2)
>
> gives you 50.
>
> "AB" wrote:
>
> > Hello All,
> >
> > How can I subtract dates to get whole numbers in Excel? I am trying to link
> > DOB's with a chart that rates based on age.
> >
> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >
> > Any and all assistance is greatly appreciated.
> >
> > Thanks.
> >
```
 0
AB (54)
12/20/2005 7:54:01 PM
```you need to change your formatting for the resula cell so it won't give you a
date:
change the formatting for 02/19/1900 cell by:

format-->cell-->number-->change to number

"AB" wrote:

> Nikki,
>
> Thanks.  I tried that but it gave me 02/19/1900.
>
>
> "Nikki" wrote:
>
> > A1: 12/20/2005
> > A2: 12/20/1955
> > formual
> > =year(a1)-year(a2)
> >
> > gives you 50.
> >
> > "AB" wrote:
> >
> > > Hello All,
> > >
> > > How can I subtract dates to get whole numbers in Excel? I am trying to link
> > > DOB's with a chart that rates based on age.
> > >
> > > example:  12/20/2005 - 12/20/1955 = 50 years old.
> > >
> > > Any and all assistance is greatly appreciated.
> > >
> > > Thanks.
> > >
```
 0
Nikki (101)
12/20/2005 8:05:02 PM
```Hi Chip,

I think I have it.  I tried using the formula listed below however what
stumps me is the follow:

=DATEDIF(09/17/1943,TODAY(),"Y")

I tried putting todays date in between the brackes next to TODAY but it di d
not work.  What am I doing wrong?

Thanks again for all of your help.

"Chip Pearson" wrote:

> I guess I don't really understand your problem. If you have a
> birthdate, the formula
>
> =DATEDIF(birth_date,TODAY(),"Y")
>
> will return the age in years.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "AB" <AB@discussions.microsoft.com> wrote in message
> news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
> > Chip,
> >
> > Thanks for your response.  I tried that but it did not do what
> > I wanted it
> > to.  What I am trying to do is the following.  I have a list of
> > employees.  I am trying to put together a formular where I can
> > tell Excel to
> > calculate the birthdate and then based on its results do a
> > VLookup to see
> > what the coresponding rate is and then have it pull that rate
> > into a
> > specified cell.  For example, is Bob Smith was born on
> > 12/20/1950, I need
> > exce to recognize that he is 50 years of age.  Then look at
> > what rate
> > corresponds to 50 years of age and then put it in Cell A4.
> >
> > Please let me know if this makes sense or if you need
> >
> > Thanks.
> >
> >
> >
> >
> > Chip Pearson" wrote:
> >
> >> Use the DATEDIF function.
> >>
> >> =DATEDIF(start_date,end_date,"Y")
> >>
> >> www.cpearson.com/excel/datedif.htm .
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> > Hello All,
> >> >
> >> > How can I subtract dates to get whole numbers in Excel? I am
> >> > trying to link
> >> > DOB's with a chart that rates based on age.
> >> >
> >> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >> >
> >> > Any and all assistance is greatly appreciated.
> >> >
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>
>
```
 0
AB (54)
12/20/2005 8:06:03 PM
```In your formula,

=DATEDIF(09/17/1943,TODAY(),"Y")

the / characters are treated as division operators, not date
separators. Use something like

=DATEDIF(DATE(1943,9,17),TODAY(),"y")

or

=DATEDIF("09/17/1943",TODAY(),"Y")

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"AB" <AB@discussions.microsoft.com> wrote in message
news:0D8069F6-ABDA-4CAF-A874-48DD3B01CC7F@microsoft.com...
> Hi Chip,
>
> I think I have it.  I tried using the formula listed below
> however what
> stumps me is the follow:
>
> =DATEDIF(09/17/1943,TODAY(),"Y")
>
> I tried putting todays date in between the brackes next to
> TODAY but it di d
> not work.  What am I doing wrong?
>
> Thanks again for all of your help.
>
>
> "Chip Pearson" wrote:
>
>> I guess I don't really understand your problem. If you have a
>> birthdate, the formula
>>
>> =DATEDIF(birth_date,TODAY(),"Y")
>>
>> will return the age in years.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>> "AB" <AB@discussions.microsoft.com> wrote in message
>> news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
>> > Chip,
>> >
>> > Thanks for your response.  I tried that but it did not do
>> > what
>> > I wanted it
>> > to.  What I am trying to do is the following.  I have a list
>> > of
>> > employees.  I am trying to put together a formular where I
>> > can
>> > tell Excel to
>> > calculate the birthdate and then based on its results do a
>> > VLookup to see
>> > what the coresponding rate is and then have it pull that
>> > rate
>> > into a
>> > specified cell.  For example, is Bob Smith was born on
>> > 12/20/1950, I need
>> > exce to recognize that he is 50 years of age.  Then look at
>> > what rate
>> > corresponds to 50 years of age and then put it in Cell A4.
>> >
>> > Please let me know if this makes sense or if you need
>> >
>> > Thanks.
>> >
>> >
>> >
>> >
>> > Chip Pearson" wrote:
>> >
>> >> Use the DATEDIF function.
>> >>
>> >> =DATEDIF(start_date,end_date,"Y")
>> >>
>> >> www.cpearson.com/excel/datedif.htm .
>> >>
>> >>
>> >> --
>> >> Cordially,
>> >> Chip Pearson
>> >> Microsoft MVP - Excel
>> >> Pearson Software Consulting, LLC
>> >> www.cpearson.com
>> >>
>> >>
>> >> "AB" <AB@discussions.microsoft.com> wrote in message
>> >> > Hello All,
>> >> >
>> >> > How can I subtract dates to get whole numbers in Excel? I
>> >> > am
>> >> > trying to link
>> >> > DOB's with a chart that rates based on age.
>> >> >
>> >> > example:  12/20/2005 - 12/20/1955 = 50 years old.
>> >> >
>> >> > Any and all assistance is greatly appreciated.
>> >> >
>> >> > Thanks.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

```
 0
chip1 (1821)
12/20/2005 8:12:00 PM
```Here is an example:
Here is the chart with the ages and their coresponding rates.
Per \$1000 of coverage
<25	\$1.20
25-29	\$0.96
30-34	\$1.20
35-39	\$1.44
40-44	\$1.80
45-49	\$2.76
50-54	\$4.68
55-59	\$8.64
60-64	\$13.08
65-69	\$19.68
70+	\$46.68

What I want to be able to do is the following

DOB                   Age                 Rate
09/17/1943            ?                 \$13.08/1000

I want Excel to be able to take the birth date calucalte the age then find
the coresponding rate.

Thanks.

Chip Pearson" wrote:

> I guess I don't really understand your problem. If you have a
> birthdate, the formula
>
> =DATEDIF(birth_date,TODAY(),"Y")
>
> will return the age in years.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "AB" <AB@discussions.microsoft.com> wrote in message
> news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
> > Chip,
> >
> > Thanks for your response.  I tried that but it did not do what
> > I wanted it
> > to.  What I am trying to do is the following.  I have a list of
> > employees.  I am trying to put together a formular where I can
> > tell Excel to
> > calculate the birthdate and then based on its results do a
> > VLookup to see
> > what the coresponding rate is and then have it pull that rate
> > into a
> > specified cell.  For example, is Bob Smith was born on
> > 12/20/1950, I need
> > exce to recognize that he is 50 years of age.  Then look at
> > what rate
> > corresponds to 50 years of age and then put it in Cell A4.
> >
> > Please let me know if this makes sense or if you need
> >
> > Thanks.
> >
> >
> >
> >
> > Chip Pearson" wrote:
> >
> >> Use the DATEDIF function.
> >>
> >> =DATEDIF(start_date,end_date,"Y")
> >>
> >> www.cpearson.com/excel/datedif.htm .
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> > Hello All,
> >> >
> >> > How can I subtract dates to get whole numbers in Excel? I am
> >> > trying to link
> >> > DOB's with a chart that rates based on age.
> >> >
> >> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >> >
> >> > Any and all assistance is greatly appreciated.
> >> >
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>
>
```
 0
AB (54)
12/20/2005 8:16:04 PM
```Perfect.  That worked.  One last question.  Can I set it up where I can
reference the cell that the date is in so that I can use the formula for the
other birthdates and keep TODAY's date as an absolute?

Thanks again for all of your help.

"Chip Pearson" wrote:

>
> =DATEDIF(09/17/1943,TODAY(),"Y")
>
> the / characters are treated as division operators, not date
> separators. Use something like
>
> =DATEDIF(DATE(1943,9,17),TODAY(),"y")
>
> or
>
> =DATEDIF("09/17/1943",TODAY(),"Y")
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "AB" <AB@discussions.microsoft.com> wrote in message
> news:0D8069F6-ABDA-4CAF-A874-48DD3B01CC7F@microsoft.com...
> > Hi Chip,
> >
> > I think I have it.  I tried using the formula listed below
> > however what
> > stumps me is the follow:
> >
> > =DATEDIF(09/17/1943,TODAY(),"Y")
> >
> > I tried putting todays date in between the brackes next to
> > TODAY but it di d
> > not work.  What am I doing wrong?
> >
> > Thanks again for all of your help.
> >
> >
> > "Chip Pearson" wrote:
> >
> >> I guess I don't really understand your problem. If you have a
> >> birthdate, the formula
> >>
> >> =DATEDIF(birth_date,TODAY(),"Y")
> >>
> >> will return the age in years.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
> >> > Chip,
> >> >
> >> > Thanks for your response.  I tried that but it did not do
> >> > what
> >> > I wanted it
> >> > to.  What I am trying to do is the following.  I have a list
> >> > of
> >> > employees.  I am trying to put together a formular where I
> >> > can
> >> > tell Excel to
> >> > calculate the birthdate and then based on its results do a
> >> > VLookup to see
> >> > what the coresponding rate is and then have it pull that
> >> > rate
> >> > into a
> >> > specified cell.  For example, is Bob Smith was born on
> >> > 12/20/1950, I need
> >> > exce to recognize that he is 50 years of age.  Then look at
> >> > what rate
> >> > corresponds to 50 years of age and then put it in Cell A4.
> >> >
> >> > Please let me know if this makes sense or if you need
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> > Chip Pearson" wrote:
> >> >
> >> >> Use the DATEDIF function.
> >> >>
> >> >> =DATEDIF(start_date,end_date,"Y")
> >> >>
> >> >> www.cpearson.com/excel/datedif.htm .
> >> >>
> >> >>
> >> >> --
> >> >> Cordially,
> >> >> Chip Pearson
> >> >> Microsoft MVP - Excel
> >> >> Pearson Software Consulting, LLC
> >> >> www.cpearson.com
> >> >>
> >> >>
> >> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> >> > Hello All,
> >> >> >
> >> >> > How can I subtract dates to get whole numbers in Excel? I
> >> >> > am
> >> >> > trying to link
> >> >> > DOB's with a chart that rates based on age.
> >> >> >
> >> >> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >> >> >
> >> >> > Any and all assistance is greatly appreciated.
> >> >> >
> >> >> > Thanks.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
```
 0
AB (54)
12/20/2005 8:26:02 PM
```Chip,

Thanks for all of your help.  I figured it out and it worked.

"Chip Pearson" wrote:

>
> =DATEDIF(09/17/1943,TODAY(),"Y")
>
> the / characters are treated as division operators, not date
> separators. Use something like
>
> =DATEDIF(DATE(1943,9,17),TODAY(),"y")
>
> or
>
> =DATEDIF("09/17/1943",TODAY(),"Y")
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "AB" <AB@discussions.microsoft.com> wrote in message
> news:0D8069F6-ABDA-4CAF-A874-48DD3B01CC7F@microsoft.com...
> > Hi Chip,
> >
> > I think I have it.  I tried using the formula listed below
> > however what
> > stumps me is the follow:
> >
> > =DATEDIF(09/17/1943,TODAY(),"Y")
> >
> > I tried putting todays date in between the brackes next to
> > TODAY but it di d
> > not work.  What am I doing wrong?
> >
> > Thanks again for all of your help.
> >
> >
> > "Chip Pearson" wrote:
> >
> >> I guess I don't really understand your problem. If you have a
> >> birthdate, the formula
> >>
> >> =DATEDIF(birth_date,TODAY(),"Y")
> >>
> >> will return the age in years.
> >>
> >>
> >> --
> >> Cordially,
> >> Chip Pearson
> >> Microsoft MVP - Excel
> >> Pearson Software Consulting, LLC
> >> www.cpearson.com
> >>
> >>
> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> news:2A5671AB-944E-4DEA-A8FC-2FFD6E1473D6@microsoft.com...
> >> > Chip,
> >> >
> >> > Thanks for your response.  I tried that but it did not do
> >> > what
> >> > I wanted it
> >> > to.  What I am trying to do is the following.  I have a list
> >> > of
> >> > employees.  I am trying to put together a formular where I
> >> > can
> >> > tell Excel to
> >> > calculate the birthdate and then based on its results do a
> >> > VLookup to see
> >> > what the coresponding rate is and then have it pull that
> >> > rate
> >> > into a
> >> > specified cell.  For example, is Bob Smith was born on
> >> > 12/20/1950, I need
> >> > exce to recognize that he is 50 years of age.  Then look at
> >> > what rate
> >> > corresponds to 50 years of age and then put it in Cell A4.
> >> >
> >> > Please let me know if this makes sense or if you need
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> > Chip Pearson" wrote:
> >> >
> >> >> Use the DATEDIF function.
> >> >>
> >> >> =DATEDIF(start_date,end_date,"Y")
> >> >>
> >> >> www.cpearson.com/excel/datedif.htm .
> >> >>
> >> >>
> >> >> --
> >> >> Cordially,
> >> >> Chip Pearson
> >> >> Microsoft MVP - Excel
> >> >> Pearson Software Consulting, LLC
> >> >> www.cpearson.com
> >> >>
> >> >>
> >> >> "AB" <AB@discussions.microsoft.com> wrote in message
> >> >> > Hello All,
> >> >> >
> >> >> > How can I subtract dates to get whole numbers in Excel? I
> >> >> > am
> >> >> > trying to link
> >> >> > DOB's with a chart that rates based on age.
> >> >> >
> >> >> > example:  12/20/2005 - 12/20/1955 = 50 years old.
> >> >> >
> >> >> > Any and all assistance is greatly appreciated.
> >> >> >
> >> >> > Thanks.
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
```
 0
AB (54)
12/20/2005 8:55:03 PM

Similar Artilces:

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

Excel Mail Merger Problem
When merging excel with word for..my currrency field is dropping off zeros to the right of decimal point..if field has \$ 56.03, it imports correctly, but if it is \$56.00, it comes in as \$56, if it is \$56.30, it comes in as \$56.3...this process is for invoices..any advice (I've tried everything)? Debra Dalgleish has given the following reply in newsgroups http://google.com/groups?threadm=3EBB0685.5080002%40contextures.com and has also identified the following MS KB articles in such as : http://google.com/groups?threadm=3DFA8DA8.6000209%40contextures.com 304387 - WD: Date, Phone N...

excel charts #24
Is it possible to freeze a chart in excel and then add another chart with different column widths without having the changes affect the first chart? Hi, Move off the chart, press the Shift or Ctrl keys and select the chart. Then right-click it and choose Format Object On the Properties sheet select Don't move or size with cells -- Thanks, Shane Devenshire "Lorena" wrote: > Is it possible to freeze a chart in excel and then add another chart with > different column widths without having the changes affect the first chart? ...

How do I prevent "Help" from opening when I open Excel?
Whenever I open Excel 2003 the Help feature opens on the right. How do I pevent this? I only want Excel to open with a new sheet. -- Lee P Lee Help or TaskPane. For Help, you should be able to close it using the "x" and next time open Excel should not display. If TaskPane, go to Tools>Options>View and uncheck "Startup Task Pane" Gord Dibben Excel MVP On Mon, 12 Dec 2005 13:05:29 -0800, "Lee P" <LeeP@discussions.microsoft.com> wrote: >Whenever I open Excel 2003 the Help feature opens on the right. >How do I pevent this? I only want Exce...

pivot table #21
I have a question on Excel. When I update a pivot table, I used to be able to hold down the shift and ctrl keys and highlight the area, but lately I found that I cannot use this short cut method. Is there another short cut method? Thanks for your help. In step two of the Pivot Wizard, you should be able to select a starting cell on the worksheet, then hold the Shift key, and tap the End key, then the Down or Right arrow key, to select a range of cells. Or, base the pivot table on a dynamic range, which will expand automatically as new records are added. There are instructions here:...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Message "class not registered" opening sheet with macros Excel 97
I have created a document with Macros and is password protected. I have sent it to several people who are able to use it - the one who can't is using Excel 97 SP2. They are getting error message "class not registered" - could it be the version they are using and how do I make sure they are able to use it. This could simply be a version issue if you developed on a later version and used controls from that versions object libraries. You should always use the lowest version to develop on. If this is not the case look in the VBE on the faulty machine and check tools>ref...

Excel 2002
Have several spreadsheet files I use routinely. Three have recently crashed after I added another sheet. In each case the document recovery created a file missing all the color and text formats that the file contained before the crash. Not sure what other changes may have occurred. Is there something wrong with the copy of Excel on my PC? Could these three files be corrupt? Is there a procedure to "clean-up" these files? Thanks in advance for any suggestions. Mark Hi sounds like they are corrupted. I would suggest to copy the data + formats to a new, 'fresh' workbook. ...

lost data when opening excel workbooks ; text import wizard popup
When opening many of my excel files ,which all have the same modification date, I come across the text import wizard which states that my text in these files is 'delimited'. All of the files ,including a few word doc.s have had their data changed to show all " y " with two dots above the letter for as far as the eye can see. No import or export has been done with the files and no modifications were done on that date, as far as I know. Is this a corruption problem or is their some 'fix' that I am overlooking. Thanks for any ideas. ...

conversion lotus 123 files to excel -- problem
I am converting lotus123 files to excel2002. One problem is that in lotus, literals are ignored when found in a cell within a formula. Excel on the other hand is not doing this and therefore causing #value errors on all the formulas where this occurs. Is there a way to handle this in excel other than manually having to change all the formulas? ...

Excel, how do I get ALT F C to work the same as ALT F Enter C ?
The above is an example, but it aplies to any menu. Before, if I pressed say, ALT F, the drop down menu would appear and I could press, say, C and get to the submenu. Now I have to press Enter, before the C, which is a bother. Thanks. On my Windows 2000 version 5.00.2195 with Excel '97 and Excel 2003 the Alt F C still works. What version software / Excel are you using? -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059 View this thread: http://www....

DoModal() question.
I have a dialog class dlg. When I use dlg.DoModal(), there're always two buttons "Cancel" and "Ok" on popup window which I didn't put there. They seem to be put there by system default. How to remove them? Thanks. Frank E Rogers wrote: > I have a dialog class dlg. When I use dlg.DoModal(), there're always two > buttons "Cancel" and "Ok" on popup window which I didn't put there. They > seem to be put there by system default. How to remove them? > Thanks. > > First, you better add message handlers for them. Your ha...

How do I display two excel pages at the same time?
I want to have two excel pages displayed on my computer at the same time, but when I open both pages it uses the same master excel and when I toggle between the two it won't open both of them up. I'm using office 2003. Thanks for the help. -- snowtime ------------------------------------------------------------------------ snowtime's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25982 View this thread: http://www.excelforum.com/showthread.php?threadid=393499 Open both and then do windows>arrange and vertical (or any of the other choices, I usuall...

How do I add a hyperlink to an individual word in an Excel cell?
I am using Excel 2000 (not by choice) and I need to add a hyperlink to an individual word within the cell, not the whole cell itself. For example in the sentence "Click here or here to go to the appropriate web page." I want the words "here" to each have a separate hyperlink. Any ideas? I can manage some VBA too if necessary. Thanks, Rosalie Hi Rosalie, You can't do that in Excel. You would have to use HTML or Word or some other means. You could fake it, the entire cell would be a link, but you could after assigning the hyperlink select another...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

How do I get excel files to open automatically from directories?
When I try to open excel files from the directory or from desktop I only get a blank worksheet not the file. I have to then go through File Open to get the file I want. There must be a way to have them open automatically. On Mon, 2 Jan 2006 21:22:01 -0800, Damian <Damian@discussions.microsoft.com> wrote: >When I try to open excel files from the directory or from desktop I only get >a blank worksheet not the file. I have to then go through File Open to get >the file I want. > >There must be a way to have them open automatically. Go to Tools -> Options -> Gen...

FIX at bottom-right of Excel screen. Know what it means?
When ever I open a worksheet the bottom right corner of the screen (by the CAPS, SCRL, etc alerts) FIX is showing. Does anyone know what is causing this? It means you have fixed decimal settings checked under tools>options>edit -- Regards, Peo Sjoblom "Bobbert" <Bobbert@discussions.microsoft.com> wrote in message news:EFFD8EFF-D7A2-42D8-862E-E0B1F535A5BE@microsoft.com... > When ever I open a worksheet the bottom right corner of the screen (by the > CAPS, SCRL, etc alerts) FIX is showing. Does anyone know what is causing > this? Tools>Options&g...

How do I easily draw in Excel the Upper and Lower limits of a con.
How do I easily draw in Excel the Upper and Lower limits of a control chart? I want to draw lines for target, min value and max values. How I can do that without having to make complete data series? Judy, Jon Peltier has just updated his site to include a Run chart that you could revise to be a control chart: http://peltiertech.com/Excel/Charts/RunChtLines.html Jon also has an example of a dynamic control chart: http://www.peltiertech.com/Excel/Charts/statscharts.html#CtrlCht ---- Regards, John Mansfield http://www.pdbook.com "Judy" wrote: > How do I easily draw in ...

simple xss question
Hello, One thing I dont understand about XSS: 1.There is a page with a text box 2. I inject some Javascript into that textbox that shows a form in a new div that can send content somewhere. Am I not the only one that sees this form? What good is it if the next person that goes to the website just sees the page sent from the server correctly? Thanks Its usually more of a problem when they get access to your filesystem or database ... As i've found out the hard way On 16/02/2010 14:51, in article 49e2f337-0f2c-46f1-87d4-b58b1275ef40@f17g2000prh.googlegroups.com, "...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

InPlace ugrade from Exchange 2000 to 2003 recovery question
Hi, Will shortly be doing an inplace upgrade from Exchange 2000 enterprise edition to Exchange 2003 Enterprise edition. After we do all the steps ie. Forestprep/domainprep etc then do the actual upgrade if the upgrade fails how do we roll back. We use Brightstor Arcserve version 11 which is supported by Exchange 2000 and we backup doing the Full method not bricks level. I have documentation on how to restore Exchange using this but this just restores the database to my knowledge (never had to do it) with exchange 2000 so how do I roll back a failed upgrade. We have to do an inplace...

Textbox's truncated once printed (Excel 2000)
When using text boxs in excel if the data entry is larger than the text box it does not print out everything in the text box. Is there a way to get it to print out all the information contained in the text box. For the only two solutions I can think of is 1) Make the text boxs bigger but my manager rejected the idea. 2)Cut an paste the information onto a excel spreedsheet then print it that way by using a macro but that is a last resort. Dear Robert, 1) 2) and 3) Reject your manager. If it doesn't fit, it doesn't. 4) Perhaps ... use a smaller font? 5) Refrase your text ... Fran...

Publishing Microsoft Excel Charts to the Web
I routinely publish Excel charts to a web site for viewing in a web browser. When I do so, the charts are too large to fit on one screen without scrolling. Is there a way to make the charts fit the size of the user's browser window when you publish them? Jim - Embed the charts in a worksheet (right click, Location, As Object In: Sheet Name), and size them to fit before exporting. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Jim wrote: > I routinely publish Excel charts to a web site f...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

Question Re: Migration of Exchange to New Server.
I'm in the process of replacing an aging Exchange server with a brand new server. Once complete the old server will be taken off line and recycled. I figure the easiest way to do this is bring the new server up as a second Exchange server within the domain and then move the mail boxes from the old server to the new server but I have a couple of questions. 1) How do I move the public folders? I'm assuming I can setup Replication between the two servers and that will migrate the public folder data. 2) What happens when the current server goes off line? Other then having to upd...