Using IF Formula with Dates

[ShreejitN]

Hello Everyone, here is my problem statement.

I have certain dates spanning several years in column A.

I want to display only the Month and Year in Column B from the date mentioned in column A

Eg:
A1 has 09-Jul-13
A2 has 09-Aug-13,
A3 has 13-Feb-2014.

B1 should show Jul-13
B2 should show Aug-13
B3 should show Feb-14

I have been trying the IF forumla but have not been able to make it work. Can someone help me out with the correct IF formula or any other way by which I can achieve the above result.

Thank You

[popipipo]

Change the celformat to 'MMM-YY'

[ShreejitN]

Thanks, that solves part of the problem. However my ultimate objective is to create a pivot table out of Column B which tell me how many dates fall under a particular month, however when i use your solution it does not help. Let me explain.

Eg:
A1 has 09-Jul-13
A2 has 11-Jul-13,
A3 has 13-Feb-2014
A4 has 21-Feb-2014
A5 has 25-Feb-2014

When I change the format
B1 is now showing Jul-13
B2 is now showing Jul-13
B3 is now showing Feb-14
B4 is now showing Feb-14
B5 is now showing Feb-14

Now I want to create a Pivot Table which should show count against Jul-13 as 2 and Feb-14 as 3, however the pivot table picks up the exact date instead of month and shows me the below output

09-Jul-13 - 1
11-Jul-13 - 1
13-Feb-2014 - 1
21-Feb-2014 - 1
25-Feb-2014 - 1

But i want it to show

July-13 - 2
Feb-14 - 3

Change the celformat to 'MMM-YY'

[XOR LX]

Hi,

Have you tried using the Group feature in the Pivot Table (right-click, Group, Months)?

Regards

[ShreejitN]

Thanks, Again that helps solve only a part of the problem.

As I mentioned I have dates spanning several years and if i have a date in the same month of different years say 21-Feb-13 and 15-Feb-2014 the Pivot just shows count of 2 against Feb whereas i am expecting it to show count 1 against Feb-13 and count 1 against Feb-14

Hi,

Have you tried using the Group feature in the Pivot Table (right-click, Group, Months)?

Regards

[XOR LX]

Then group it in Months and Years!!

[ShreejitN]

Thanks a lot, That works. It separates the years.

Is there anyway i can get it to display Feb-13, Feb-14 instead of the way it shows right now.

Right now it shows

2013
Feb-1

2014
Feb-1

I want it to show

Feb-13 - 1
Feb-14 - 1

Then group it in Months and Years!!

[XOR LX]

Not exactly, unless you overwrite the entries. The closest approximation I can think of is:

Highlight one of the years. Right-click. Move/Move Years to Right.

Then highlight one of the months. Right-click. Field Settings/Layout & Print/Show item labels in tabular form/Repeat item labels.

Regards