Month-Year 'format'

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Suzanne - 31 Mar 2008 15:59 GMT

Hello. I'm setting up a results summary table that calculates from a detail
tab.
In the detail tab i'm capturing date of activity (where the user inputs a
date) 3/31/08. On the summary tab i want to calculate results by month year.
I inserted a column next to the activity date column in the detail tab;
tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
allow me to count and group by month year. Suggestions?

Signature

Thank you -- Suzanne.

Reply to this Message

Bob Phillips - 31 Mar 2008 16:28 GMT

=SUMPRODUCT(--(YEAR(A2:A200)=2008),--(MONTH(A2:A200)=3))

as an example for March

Signature

HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Hello. I'm setting up a results summary table that calculates from a
> detail
[quoted text clipped - 6 lines]
> to
> allow me to count and group by month year. Suggestions?

Reply to this Message

Kevin B - 31 Mar 2008 16:31 GMT

Insert a helper column and create a column of dates using the following formula

=DATE(YEAR(A1),MONTH(A1),1)

Substituting the A1 in the expample with your first date cell. Copy the
formula down to the last row and then format the formula date column to
display month/year.

Now that you have monthly dates with the same day you grouping by month year
will now work as planned.

Hope this helps.

Signature

Kevin Backmann

> Hello. I'm setting up a results summary table that calculates from a detail
> tab.
[quoted text clipped - 3 lines]
> tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
> allow me to count and group by month year. Suggestions?

Reply to this Message

Suzanne - 31 Mar 2008 17:20 GMT

Thanks to all. Kevin's formuals was exactly what i was looking for. Many
thanks!

Signature

Thank you -- Suzanne.

> Insert a helper column and create a column of dates using the following formula
>
[quoted text clipped - 16 lines]
> > tried and failed at formatting the m/dd/yy format to month year (Mar-2008) to
> > allow me to count and group by month year. Suggestions?

Reply to this Message

Ron de Bruin - 31 Mar 2008 16:33 GMT

EasyFilter have a option to do this.
You can use the subtotal function if you want if you filter on the same sheet
http://www.rondebruin.nl/easyfilter.htm

Signature

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Reply to this Message

Rate this thread: