5 Replies Latest reply on Jan 11, 2011 10:05 AM by philmodjunk

    Summary and Dates



      Summary and Dates


      Good Morning Everyone,

      I'm a newbie to FMP11 and I'm trying to create a summary report based on dates, customers and invoice totals. Right now I'm at the first stage with DATES I'm using XX/XX/XX format for a start date and end date. I'm trying to figure out how I can group them based on dates like in the Home Budget Template/Summary in FMP11 with the name of the month (January, February..ect.)  Can I convert the dates from the number format in the summary and get them grouped to the names of the month?

        • 1. Re: Summary and Dates

          The dates should be in Date format.

          While you can define a calculation field to extract the number or name of the month of a date field, either option doesn't sort easily in the desired order in all cases.

          Instead, I define a calculation that I learned from Comment here in this Forum:

          DateField - Day ( DateField ) + 1

          This calculation returns a date that is the first day of the month for all dates of that month. Since this is a date field I can sort by this field and the records will be grouped correctly by month in the same order as the months are listed in the calendar and will even sort correctly if I have records from more than one year.

          When you place this calculation field on your layout, you can format it in the inspector to just display the name of the month.

          • 2. Re: Summary and Dates

            For example:

            Create two fields

            DateStartMonthNumber = Month ( DateStart )

            DateStartMointhName= MonthName ( DateStart )

            Create a sub-summary break based on DateStartMonthNumber, sort by DateStartMonthNumber, but put the DateStartMonthName field in the sub-summary section.

            • 3. Re: Summary and Dates

              I remember that calc from Comment - very good!

              • 4. Re: Summary and Dates

                Ok Phil, Im a little confused so if my date field is "start date" them caluclation for my new field will be

                start date - Day ( start date ) + 1

                is that right?

                • 5. Re: Summary and Dates

                  Yes, and if start date is January, 3, 2011, this calculation returns January 1, 2011. If you enter any other January 2011 date you get the same date, 1/1/2011.

                  Make sure that this calcualtion is set to return "Date" as it's data type.