Formula Question
 

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

hi
1. when excel doesn't respond to number and date format changes, it's
usually because excel is seeing the data as text. try this.
in a cell off the the side, enter a 1. copy it. highlight the data and paste
special multipy.

2 =countif(b2:B500),"X") adjust to suit. you may need a formula for each
project you wish to count.

regards
FSt1

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

HOW DO I ASK A QUESTION?

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

Thank you for the information -
I did as you suggested in Item # 1 and it returned as text vs. dates (is
there a next step? to get it back to dates so I can pull the year out
separately?

I will try Item # 2 now - I just needed the formula. I think I was close -
had the right idea, and yes I think I have to specify each project I want
counted.




--
Alison


"FSt1" wrote:

hi
1. when excel doesn't respond to number and date format changes, it's
usually because excel is seeing the data as text. try this.
in a cell off the the side, enter a 1. copy it. highlight the data and paste
special multipy.

2 =countif(b2:B500),"X") adjust to suit. you may need a formula for each
project you wish to count.

regards
FSt1

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

do you need to ask me a question? or generally?


--
Alison


"NANCY" wrote:

HOW DO I ASK A QUESTION?

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

Hi Alison,

If the date is formatted as text, then:

=RIGHT(A1,4)*1

multiplying by one converts text to a number.

If you want to convert text mm/dd/yyyy to a number

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

To count duplicate project ID's:

=COUNTIF(B2:B100,"X")

Hope this helps.

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison

Formula Question
 

Hi Nancy,

Click on the New drop down arrow and selection question. This link is just
under the Search For field at the top of the screen above the list of posts.

"NANCY" wrote:

HOW DO I ASK A QUESTION?

"Alison" wrote:

I have exported a report from Access to Excel 2003 ... I have two questions
which would make my life much easier.

One - the date field is formatted mm/dd/yyyy - I believe since it was from
Access I am unable to format it differently as when I go to change date
format it does not work. The end result I am looking for is to record the
year from the date field and populate a separate cell with the year only in
it.

Two - the information is a list with various duplicate project ID's that i
have to count. For example, "If B2=x, count 1"


--
Alison