software

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 quot;not availablequot; in the corresponding cell. I konw that quot;blank cellsquot;
and quot;non date format cellsquot; are not allowed for grouping.
Is there any other way of adding these quot;not availablequot; text to be included
in the grouped columns by month?
Thanks

Hi

In your original table, search and replace the quot;not availablequot; 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 Govierquot;kuvulmazquot; gt; wrote in message
...
gt; Hi,
gt; I have a column for payment due dates on my Excel table (Office 2003 -
gt; Windows XP Pro - SP2) which I would like to group by months with a
gt; pivot
gt; table, however some of the dates in the column are not available
gt; exactly so
gt; it says quot;not availablequot; in the corresponding cell. I konw that quot;blank
gt; cellsquot;
gt; and quot;non date format cellsquot; are not allowed for grouping.
gt; Is there any other way of adding these quot;not availablequot; text to be
gt; included
gt; in the grouped columns by month?
gt; Thanks
Thanks but I already tried using quot;dummy datesquot; 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 quot;what the heck is this?quot; then I need to explain and you know...

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

Thanks..
Tarkan Kuvulmaz

quot;Roger Govierquot; gt; wrote in message
...
gt; Hi
gt;
gt; In your original table, search and replace the quot;not availablequot; cells with
gt; a date that cannot be confused with your real data - either a date way in
gt; the future or way in the past.
gt; Then choose group by Date, but make the range to group be a range that
gt; excludes this early or late date.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt; quot;kuvulmazquot; gt; wrote in message
gt; ...
gt;gt; Hi,
gt;gt; I have a column for payment due dates on my Excel table (Office 2003 -
gt;gt; Windows XP Pro - SP2) which I would like to group by months with a pivot
gt;gt; table, however some of the dates in the column are not available exactly
gt;gt; so
gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that quot;blank
gt;gt; cellsquot;
gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt; included
gt;gt; in the grouped columns by month?
gt;gt; Thanks
gt;
gt;
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(A2gt;0,TEXT(A2,quot;mmmquot;),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 gt; wrote
gt; Thanks but I already tried using quot;dummy datesquot; and it does not look
gt; good, because I do not want to exclude these N/A dates in the final
gt; report of the PT, for example it should look like this after
gt; grouping by month: Month Payment
gt; January $ 10,000
gt; March $ 15,000
gt; June $ 20,000
gt; December $ 15.000
gt; Not Available $ 30,000
gt; Total $ 90,000
gt; When I put dummy date instead of N/A it does not look very
gt; professional when people see payment dates for 01.01.2222
gt; They say quot;what the heck is this?quot; then I need to explain and you
gt; know... Is there a way to do this with the Pivot table?
gt; Thanks..
gt; Tarkan Kuvulmaz
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt;gt; Hi
gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt; cells with a date that cannot be confused with your real data -
gt;gt; either a date way in the future or way in the past.
gt;gt; Then choose group by Date, but make the range to group be a range
gt;gt; that excludes this early or late date.
gt;gt; --
gt;gt; Regards
gt;gt; Roger Govier
gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi,
gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt; column are not available exactly so
gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt; quot;blank cellsquot;
gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt;gt; included
gt;gt;gt; in the grouped columns by month?
gt;gt;gt; ThanksHi,
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 quot;B2quot; it
says quot;The formula you typed contains an errorquot;. Could you please once again
check the formula?

Thanks,
Tarkan

quot;Roger Govierquot; gt; wrote in message
...
gt; Hi
gt;
gt; Then I would add an extra column in your source data table.
gt; Assuming your dates are in column A, then create a new column with a
gt; header of Month and insert the following formual and copy down
gt; =IF(A2gt;0,TEXT(A2,quot;mmmquot;),A2)
gt; On the PT, use the new Month column rather than Date.
gt; There will be no need to group.
gt;
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt; Thanks but I already tried using quot;dummy datesquot; and it does not look
gt;gt; good, because I do not want to exclude these N/A dates in the final
gt;gt; report of the PT, for example it should look like this after
gt;gt; grouping by month: Month Payment
gt;gt; January $ 10,000
gt;gt; March $ 15,000
gt;gt; June $ 20,000
gt;gt; December $ 15.000
gt;gt; Not Available $ 30,000
gt;gt; Total $ 90,000
gt;gt; When I put dummy date instead of N/A it does not look very
gt;gt; professional when people see payment dates for 01.01.2222
gt;gt; They say quot;what the heck is this?quot; then I need to explain and you
gt;gt; know... Is there a way to do this with the Pivot table?
gt;gt; Thanks..
gt;gt; Tarkan Kuvulmaz
gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi
gt;gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt;gt; cells with a date that cannot be confused with your real data -
gt;gt;gt; either a date way in the future or way in the past.
gt;gt;gt; Then choose group by Date, but make the range to group be a range
gt;gt;gt; that excludes this early or late date.
gt;gt;gt; --
gt;gt;gt; Regards
gt;gt;gt; Roger Govier
gt;gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Hi,
gt;gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt;gt; column are not available exactly so
gt;gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt;gt; quot;blank cellsquot;
gt;gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt;gt;gt; included
gt;gt;gt;gt; in the grouped columns by month?
gt;gt;gt;gt; Thanks
gt;
gt;
gt;
gt;
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(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
Otherwise, how are your dates entered? If they are true Excel dates,
then testing for gt;0 should work.

--
Regards

Roger Govier

Tarkan @ Hairline Clinic gt; wrote
gt; Hi,
gt; Thanks for this idea, it seems that it is going to work when dates
gt; are displayed as text in another column but when I enter the
gt; formula in quot;B2quot; it says quot;The formula you typed contains an errorquot;.
gt; Could you please once again check the formula?
gt; Thanks,
gt; Tarkan
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt;gt; Hi
gt;gt; Then I would add an extra column in your source data table.
gt;gt; Assuming your dates are in column A, then create a new column with a
gt;gt; header of Month and insert the following formual and copy down
gt;gt; =IF(A2gt;0,TEXT(A2,quot;mmmquot;),A2)
gt;gt; On the PT, use the new Month column rather than Date.
gt;gt; There will be no need to group.
gt;gt; Regards
gt;gt; Roger Govier
gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt; Thanks but I already tried using quot;dummy datesquot; and it does not look
gt;gt;gt; good, because I do not want to exclude these N/A dates in the final
gt;gt;gt; report of the PT, for example it should look like this after
gt;gt;gt; grouping by month: Month Payment
gt;gt;gt; January $ 10,000
gt;gt;gt; March $ 15,000
gt;gt;gt; June $ 20,000
gt;gt;gt; December $ 15.000
gt;gt;gt; Not Available $ 30,000
gt;gt;gt; Total $ 90,000
gt;gt;gt; When I put dummy date instead of N/A it does not look very
gt;gt;gt; professional when people see payment dates for 01.01.2222
gt;gt;gt; They say quot;what the heck is this?quot; then I need to explain and you
gt;gt;gt; know... Is there a way to do this with the Pivot table?
gt;gt;gt; Thanks..
gt;gt;gt; Tarkan Kuvulmaz
gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Hi
gt;gt;gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt;gt;gt; cells with a date that cannot be confused with your real data -
gt;gt;gt;gt; either a date way in the future or way in the past.
gt;gt;gt;gt; Then choose group by Date, but make the range to group be a range
gt;gt;gt;gt; that excludes this early or late date.
gt;gt;gt;gt; --
gt;gt;gt;gt; Regards
gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; Hi,
gt;gt;gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt;gt;gt; column are not available exactly so
gt;gt;gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt;gt;gt; quot;blank cellsquot;
gt;gt;gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt;gt;gt;gt; included
gt;gt;gt;gt;gt; in the grouped columns by month?
gt;gt;gt;gt;gt; Thanks
Hi,
Regional settings:
Numbers Tab - List separator = quot;;quot;
Time Tab - Time Separator= quot;:quot;
Date Tab - Date Seperator= quot;:quot;

=IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
This one does not give an error however it returns all values as quot;mmmquot;

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

Thanks for your time..

Tarkan
quot;Roger Govierquot; gt; wrote in message
...
gt; Hi
gt;
gt; What is the separator that you use in your Regional Settings? For the UK
gt; is is a comma. Maybe you use the semicolon;
gt;
gt; =IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
gt; Otherwise, how are your dates entered? If they are true Excel dates, then
gt; testing for gt;0 should work.
gt;
gt; --
gt; Regards
gt;
gt; Roger Govier
gt;
gt;
gt;
gt;
gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt; Hi,
gt;gt; Thanks for this idea, it seems that it is going to work when dates
gt;gt; are displayed as text in another column but when I enter the
gt;gt; formula in quot;B2quot; it says quot;The formula you typed contains an errorquot;.
gt;gt; Could you please once again check the formula?
gt;gt; Thanks,
gt;gt; Tarkan
gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi
gt;gt;gt; Then I would add an extra column in your source data table.
gt;gt;gt; Assuming your dates are in column A, then create a new column with a
gt;gt;gt; header of Month and insert the following formual and copy down
gt;gt;gt; =IF(A2gt;0,TEXT(A2,quot;mmmquot;),A2)
gt;gt;gt; On the PT, use the new Month column rather than Date.
gt;gt;gt; There will be no need to group.
gt;gt;gt; Regards
gt;gt;gt; Roger Govier
gt;gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt;gt; Thanks but I already tried using quot;dummy datesquot; and it does not look
gt;gt;gt;gt; good, because I do not want to exclude these N/A dates in the final
gt;gt;gt;gt; report of the PT, for example it should look like this after
gt;gt;gt;gt; grouping by month: Month Payment
gt;gt;gt;gt; January $ 10,000
gt;gt;gt;gt; March $ 15,000
gt;gt;gt;gt; June $ 20,000
gt;gt;gt;gt; December $ 15.000
gt;gt;gt;gt; Not Available $ 30,000
gt;gt;gt;gt; Total $ 90,000
gt;gt;gt;gt; When I put dummy date instead of N/A it does not look very
gt;gt;gt;gt; professional when people see payment dates for 01.01.2222
gt;gt;gt;gt; They say quot;what the heck is this?quot; then I need to explain and you
gt;gt;gt;gt; know... Is there a way to do this with the Pivot table?
gt;gt;gt;gt; Thanks..
gt;gt;gt;gt; Tarkan Kuvulmaz
gt;gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; Hi
gt;gt;gt;gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt;gt;gt;gt; cells with a date that cannot be confused with your real data -
gt;gt;gt;gt;gt; either a date way in the future or way in the past.
gt;gt;gt;gt;gt; Then choose group by Date, but make the range to group be a range
gt;gt;gt;gt;gt; that excludes this early or late date.
gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt; Regards
gt;gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt; Hi,
gt;gt;gt;gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt;gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt;gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt;gt;gt;gt; column are not available exactly so
gt;gt;gt;gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt;gt;gt;gt; quot;blank cellsquot;
gt;gt;gt;gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt;gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt;gt;gt;gt;gt; included
gt;gt;gt;gt;gt;gt; in the grouped columns by month?
gt;gt;gt;gt;gt;gt; Thanks
gt;
gt;
gt;
gt;
gt;
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

quot;Tarkan @ Hairline Clinicquot; gt; wrote in message
...
gt; Hi,
gt; Regional settings:
gt; Numbers Tab - List separator = quot;;quot;
gt; Time Tab - Time Separator= quot;:quot;
gt; Date Tab - Date Seperator= quot;:quot;
gt;
gt; =IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
gt; This one does not give an error however it returns all values as quot;mmmquot;
gt;
gt; Dates are real Excel dates I think because when I check with
gt; quot;=Isnumber(A1)quot; they all return TRUE..
gt;
gt; Thanks for your time..
gt;
gt; Tarkan
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Roger Govierquot; gt; wrote in message
gt; ...
gt;gt; Hi
gt;gt;
gt;gt; What is the separator that you use in your Regional Settings? For the UK
gt;gt; is is a comma. Maybe you use the semicolon;
gt;gt;
gt;gt; =IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
gt;gt; Otherwise, how are your dates entered? If they are true Excel dates, then
gt;gt; testing for gt;0 should work.
gt;gt;
gt;gt; --
gt;gt; Regards
gt;gt;
gt;gt; Roger Govier
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt; Hi,
gt;gt;gt; Thanks for this idea, it seems that it is going to work when dates
gt;gt;gt; are displayed as text in another column but when I enter the
gt;gt;gt; formula in quot;B2quot; it says quot;The formula you typed contains an errorquot;.
gt;gt;gt; Could you please once again check the formula?
gt;gt;gt; Thanks,
gt;gt;gt; Tarkan
gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Hi
gt;gt;gt;gt; Then I would add an extra column in your source data table.
gt;gt;gt;gt; Assuming your dates are in column A, then create a new column with a
gt;gt;gt;gt; header of Month and insert the following formual and copy down
gt;gt;gt;gt; =IF(A2gt;0,TEXT(A2,quot;mmmquot;),A2)
gt;gt;gt;gt; On the PT, use the new Month column rather than Date.
gt;gt;gt;gt; There will be no need to group.
gt;gt;gt;gt; Regards
gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt;gt;gt; Thanks but I already tried using quot;dummy datesquot; and it does not look
gt;gt;gt;gt;gt; good, because I do not want to exclude these N/A dates in the final
gt;gt;gt;gt;gt; report of the PT, for example it should look like this after
gt;gt;gt;gt;gt; grouping by month: Month Payment
gt;gt;gt;gt;gt; January $ 10,000
gt;gt;gt;gt;gt; March $ 15,000
gt;gt;gt;gt;gt; June $ 20,000
gt;gt;gt;gt;gt; December $ 15.000
gt;gt;gt;gt;gt; Not Available $ 30,000
gt;gt;gt;gt;gt; Total $ 90,000
gt;gt;gt;gt;gt; When I put dummy date instead of N/A it does not look very
gt;gt;gt;gt;gt; professional when people see payment dates for 01.01.2222
gt;gt;gt;gt;gt; They say quot;what the heck is this?quot; then I need to explain and you
gt;gt;gt;gt;gt; know... Is there a way to do this with the Pivot table?
gt;gt;gt;gt;gt; Thanks..
gt;gt;gt;gt;gt; Tarkan Kuvulmaz
gt;gt;gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt; Hi
gt;gt;gt;gt;gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt;gt;gt;gt;gt; cells with a date that cannot be confused with your real data -
gt;gt;gt;gt;gt;gt; either a date way in the future or way in the past.
gt;gt;gt;gt;gt;gt; Then choose group by Date, but make the range to group be a range
gt;gt;gt;gt;gt;gt; that excludes this early or late date.
gt;gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt;gt; Regards
gt;gt;gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt;gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt;gt; Hi,
gt;gt;gt;gt;gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt;gt;gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt;gt;gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt;gt;gt;gt;gt; column are not available exactly so
gt;gt;gt;gt;gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt;gt;gt;gt;gt; quot;blank cellsquot;
gt;gt;gt;gt;gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt;gt;gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to be
gt;gt;gt;gt;gt;gt;gt; included
gt;gt;gt;gt;gt;gt;gt; in the grouped columns by month?
gt;gt;gt;gt;gt;gt;gt; Thanks
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Hi Tarkan

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

--
Regards

Roger Govierquot;Tarkan @ Hairline Clinicquot; gt; wrote in message
...
gt; sorted now, my regional settings was causing the problems, I corrected
gt; them and it works fine now.
gt;
gt; Thank you very much for your time and effort.
gt;
gt; Best Regards,
gt; Tarkan
gt;
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Tarkan @ Hairline Clinicquot; gt; wrote in message
gt; ...
gt;gt; Hi,
gt;gt; Regional settings:
gt;gt; Numbers Tab - List separator = quot;;quot;
gt;gt; Time Tab - Time Separator= quot;:quot;
gt;gt; Date Tab - Date Seperator= quot;:quot;
gt;gt;
gt;gt; =IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
gt;gt; This one does not give an error however it returns all values as
gt;gt; quot;mmmquot;
gt;gt;
gt;gt; Dates are real Excel dates I think because when I check with
gt;gt; quot;=Isnumber(A1)quot; they all return TRUE..
gt;gt;
gt;gt; Thanks for your time..
gt;gt;
gt;gt; Tarkan
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Hi
gt;gt;gt;
gt;gt;gt; What is the separator that you use in your Regional Settings? For
gt;gt;gt; the UK is is a comma. Maybe you use the semicolon;
gt;gt;gt;
gt;gt;gt; =IF(A2gt;0;TEXT(A2;quot;mmmquot;);A2)
gt;gt;gt; Otherwise, how are your dates entered? If they are true Excel dates,
gt;gt;gt; then testing for gt;0 should work.
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Regards
gt;gt;gt;
gt;gt;gt; Roger Govier
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt;gt; Hi,
gt;gt;gt;gt; Thanks for this idea, it seems that it is going to work when dates
gt;gt;gt;gt; are displayed as text in another column but when I enter the
gt;gt;gt;gt; formula in quot;B2quot; it says quot;The formula you typed contains an errorquot;.
gt;gt;gt;gt; Could you please once again check the formula?
gt;gt;gt;gt; Thanks,
gt;gt;gt;gt; Tarkan
gt;gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; Hi
gt;gt;gt;gt;gt; Then I would add an extra column in your source data table.
gt;gt;gt;gt;gt; Assuming your dates are in column A, then create a new column with
gt;gt;gt;gt;gt; a header of Month and insert the following formual and copy down
gt;gt;gt;gt;gt; =IF(A2gt;0,TEXT(A2,quot;mmmquot;),A2)
gt;gt;gt;gt;gt; On the PT, use the new Month column rather than Date.
gt;gt;gt;gt;gt; There will be no need to group.
gt;gt;gt;gt;gt; Regards
gt;gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt;gt; Tarkan @ Hairline Clinic gt; wrote
gt;gt;gt;gt;gt;gt; Thanks but I already tried using quot;dummy datesquot; and it does not
gt;gt;gt;gt;gt;gt; look
gt;gt;gt;gt;gt;gt; good, because I do not want to exclude these N/A dates in the
gt;gt;gt;gt;gt;gt; final
gt;gt;gt;gt;gt;gt; report of the PT, for example it should look like this after
gt;gt;gt;gt;gt;gt; grouping by month: Month Payment
gt;gt;gt;gt;gt;gt; January $ 10,000
gt;gt;gt;gt;gt;gt; March $ 15,000
gt;gt;gt;gt;gt;gt; June $ 20,000
gt;gt;gt;gt;gt;gt; December $ 15.000
gt;gt;gt;gt;gt;gt; Not Available $ 30,000
gt;gt;gt;gt;gt;gt; Total $ 90,000
gt;gt;gt;gt;gt;gt; When I put dummy date instead of N/A it does not look very
gt;gt;gt;gt;gt;gt; professional when people see payment dates for 01.01.2222
gt;gt;gt;gt;gt;gt; They say quot;what the heck is this?quot; then I need to explain and
gt;gt;gt;gt;gt;gt; you
gt;gt;gt;gt;gt;gt; know... Is there a way to do this with the Pivot table?
gt;gt;gt;gt;gt;gt; Thanks..
gt;gt;gt;gt;gt;gt; Tarkan Kuvulmaz
gt;gt;gt;gt;gt;gt; quot;Roger Govierquot; gt; wrote in message
gt;gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt;gt; Hi
gt;gt;gt;gt;gt;gt;gt; In your original table, search and replace the quot;not availablequot;
gt;gt;gt;gt;gt;gt;gt; cells with a date that cannot be confused with your real data -
gt;gt;gt;gt;gt;gt;gt; either a date way in the future or way in the past.
gt;gt;gt;gt;gt;gt;gt; Then choose group by Date, but make the range to group be a
gt;gt;gt;gt;gt;gt;gt; range
gt;gt;gt;gt;gt;gt;gt; that excludes this early or late date.
gt;gt;gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt;gt;gt; Regards
gt;gt;gt;gt;gt;gt;gt; Roger Govier
gt;gt;gt;gt;gt;gt;gt; quot;kuvulmazquot; gt; wrote in message
gt;gt;gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt;gt;gt; Hi,
gt;gt;gt;gt;gt;gt;gt;gt; I have a column for payment due dates on my Excel table (Office
gt;gt;gt;gt;gt;gt;gt;gt; 2003 - Windows XP Pro - SP2) which I would like to group by
gt;gt;gt;gt;gt;gt;gt;gt; months with a pivot table, however some of the dates in the
gt;gt;gt;gt;gt;gt;gt;gt; column are not available exactly so
gt;gt;gt;gt;gt;gt;gt;gt; it says quot;not availablequot; in the corresponding cell. I konw that
gt;gt;gt;gt;gt;gt;gt;gt; quot;blank cellsquot;
gt;gt;gt;gt;gt;gt;gt;gt; and quot;non date format cellsquot; are not allowed for grouping.
gt;gt;gt;gt;gt;gt;gt;gt; Is there any other way of adding these quot;not availablequot; text to
gt;gt;gt;gt;gt;gt;gt;gt; be included
gt;gt;gt;gt;gt;gt;gt;gt; in the grouped columns by month?
gt;gt;gt;gt;gt;gt;gt;gt; Thanks
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;