Is there a way I can remove the day from a date reference?

[Rocket28]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Is there a way I can remove the day from a date reference?<o></o>
<o></o>
I have a spreadsheet set up to total the costs for certain suppliers in a given month.<o></o>
<o></o>
however, the data i am able to get from my system shows the date as day / month / year. I want my formula to sum up any costs in the month of a certain year.<o></o>
<o></o>
Is there anything I can do to the data downloaded to show jsut the month and year? I can obviously format it, but the underlying data is still there, so it doesn’t pick up that it is the information that I require.<o></o>
<o></o>
Thanks!<o></o>

 

[T. Valko]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Is there a way I can remove the day from a date reference?<o></o>
<o></o>
I have a spreadsheet set up to total the costs for certain suppliers in a given month.<o></o>
<o></o>
however, the data i am able to get from my system shows the date as day / month / year. I want my formula to sum up any costs in the month of a certain year.<o></o>
<o></o>
Is there anything I can do to the data downloaded to show jsut the month and year? I can obviously format it, but the underlying data is still there, so it doesn’t pick up that it is the information that I require.<o></o>
<o></o>
Thanks!<o></o>

You can use another cell that returns only the month and year.

If A1 = some date like 25/3/2011 then what result do you want?

This will return the TEXT string Mar 2011.

=TEXT(A1,"mmm yyyy")

 

[njimack]

Or you could sum them based on date and year, something like this:
=SUMPRODUCT(--(MONTH(A1:A100)=1),--(YEAR(A1:A100)=2011),(B1:B100))

 

[Rocket28]

Text works great! Brilliant. Thanks for this.

 

[T. Valko]

Text works great! Brilliant. Thanks for this.

You're welcome. Thanks for the feedback!