Pivot tables, problem with grouping the date column

K

kuvulmaz

Hi,
I have a column for payment due dates on my Excel table (Office 2003 -
Windows XP Pro - SP2) which I would like to group by months with a pivot
table, however some of the dates in the column are not available exactly so
it says "not available" in the corresponding cell. I konw that "blank cells"
and "non date format cells" are not allowed for grouping.
Is there any other way of adding these "not available" text to be included
in the grouped columns by month?
Thanks
 
R

Roger Govier

Hi

In your original table, search and replace the "not available" cells
with a date that cannot be confused with your real data - either a date
way in the future or way in the past.
Then choose group by Date, but make the range to group be a range that
excludes this early or late date.
 
T

Tarkan @ Hairline Clinic

Thanks but I already tried using "dummy dates" and it does not look good,
because I do not want to exclude these N/A dates in the final report of the
PT, for example it should look like this after grouping by month:

Month Payment
January $ 10,000
March $ 15,000
June $ 20,000
December $ 15.000
Not Available $ 30,000
Total $ 90,000

When I put dummy date instead of N/A it does not look very professional when
people see payment dates for 01.01.2222 :)
They say "what the heck is this?" :) then I need to explain and you know...

Is there a way to do this with the Pivot table?

Thanks..
Tarkan Kuvulmaz
 
R

Roger Govier

Hi

Then I would add an extra column in your source data table.
Assuming your dates are in column A, then create a new column with a
header of Month and insert the following formual and copy down
=IF(A2>0,TEXT(A2,"mmm"),A2)
On the PT, use the new Month column rather than Date.
There will be no need to group.

Regards

Roger Govier
 
T

Tarkan @ Hairline Clinic

Hi,
Thanks for this idea, it seems that it is going to work when dates are
displayed as text in another column but when I enter the formula in "B2" it
says "The formula you typed contains an error". Could you please once again
check the formula?

Thanks,
Tarkan
 
R

Roger Govier

Hi

What is the separator that you use in your Regional Settings? For the UK
is is a comma. Maybe you use the semicolon;

=IF(A2>0;TEXT(A2;"mmm");A2)
Otherwise, how are your dates entered? If they are true Excel dates,
then testing for >0 should work.

--
Regards

Roger Govier
 
T

Tarkan @ Hairline Clinic

Hi,
Regional settings:
Numbers Tab - List separator = ";"
Time Tab - Time Separator= ":"
Date Tab - Date Seperator= ":"

=IF(A2>0;TEXT(A2;"mmm");A2)
This one does not give an error however it returns all values as "mmm"

Dates are real Excel dates I think because when I check with "=Isnumber(A1)"
they all return TRUE..

Thanks for your time..

Tarkan
 
T

Tarkan @ Hairline Clinic

sorted now, my regional settings was causing the problems, I corrected them
and it works fine now.

Thank you very much for your time and effort.

Best Regards,
Tarkan
 
R

Roger Govier

Hi Tarkan

You're very welcome. Glad you got it sorted out on your machine, as it
was working fine for me.
 
Top