Formula Question

[Alison]

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"

 

[FSt1]

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

 

[NANCY]

HOW DO I ASK A QUESTION?

 

[Alison]

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]

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

 

[pshepard]

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.

 

[pshepard]

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.