Pivot tables, problem with grouping the date column
 

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

Pivot tables, problem with grouping the date column
 

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.

--
Regards

Roger Govier


"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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




"Roger Govier" wrote in message
...
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.

--
Regards

Roger Govier


"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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(A20,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



Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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




"Roger Govier" wrote in message
...
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(A20,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



Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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(A20;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




Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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(A20,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
Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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

=IF(A20;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






"Roger Govier" wrote in message
...
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(A20;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




Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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(A20,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
Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

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







"Tarkan @ Hairline Clinic" wrote in message
...
Hi,
Regional settings:
Numbers Tab - List separator = ";"
Time Tab - Time Separator= ":"
Date Tab - Date Seperator= ":"

=IF(A20;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






"Roger Govier" wrote in message
...
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(A20;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




Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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(A20,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
Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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

Pivot tables, problem with grouping the date column
 

Hi Tarkan

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

--
Regards

Roger Govier


"Tarkan @ Hairline Clinic" wrote in message
...
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







"Tarkan @ Hairline Clinic" wrote in message
...
Hi,
Regional settings:
Numbers Tab - List separator = ";"
Time Tab - Time Separator= ":"
Date Tab - Date Seperator= ":"

=IF(A20;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






"Roger Govier" wrote in message
...
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(A20;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




Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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(A20,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
Tarkan @ Hairline Clinic wrote
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
"Roger Govier" wrote in message
...
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.
--
Regards
Roger Govier
"kuvulmaz" wrote in message
...
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