Personalization, Customization and Productivity Tools

[mblocker1]

using dateadd function to pull daily sales so some months like January, March, etc...have 31 days and Feb has 28 or 29 and the others have 30 so on my report I have formula =if dateadd("d",-30,(?Monthenddate})={ar_invoicehistoryheader.invoicedate} then {@sales) else 0 where monthenddate is my parameter with month end and sales is taxable+nontaxable. So in this case it would pull sales with an invoice date of 1/1/12 if my month has 31 days but if I put in 2/29/12 for month end date it will put dates of 1/30/12 in first column and then 1/31/12 then the 3rd column starts 2/1/12 so I want to suppress the first two columns (or 3 if not leap year) as well as suppress first column if month only has 30 days in it (i,e if I use 4/30/12 for month end then first column is 3/31/12 and second column is 4/1/12)

[connex]

I prefer to use the DateSerial function because the month or day parameter can be increased and it will automatically resolve into the next period. This provides for simple formulas like the one below where one only needs to change the variable ClmnDay for each. The same formula works for all 31 possible days and all 12 months. Also, any day in the month works as a parameter.

Local NumberVar ClmnDay := 1;

if Month({AR_InvoiceHeader.InvoiceDate}) = Month(DateSerial(Year({?ME_Date}), Month({?ME_Date}), ClmnDay))
    Then (
            If  {AR_InvoiceHeader.InvoiceDate} =
                DateSerial(Year({?ME_Date}), Month({?ME_Date}), ClmnDay)
                    Then {@sales}
                    Else 0
        )
    Else 0

 A similar formula can be used to suppress printing in columns instead of showing zeros for days beyond actual in a given month.

[mblocker1]

Formula works great however I also used the dateadd to pull the date as title of the column but can't seem to get this new function to pull the date as a title. Can this be done?

[mblocker1]

Actually when I try and use this formula as follows to get 31 days and month end date is 4/30/12 it returns error that a day number must be between 1 and the number of days in the month

Local NumberVar ClmnDay := 31;

if Month({AR_InvoiceHistoryHeader.InvoiceDate}) = Month(DateValue(Year({?Month End Date}), Month({?Month End Date}), ClmnDay))
    Then (
            If  {AR_InvoiceHistoryHeader.InvoiceDate} =
                DateValue(Year({?Month End Date}), Month({?Month End Date}), ClmnDay)
                    Then {@sales}
                    Else 0
        )
    Else 0

[connex]

My bad, I confused the DateValue function with the DateSerial function.  I'll change the above example to use DateSerial so that you won't get that message on days with less than 31 days. 

For the labels you could use:

Local NumberVar ClmnDay := 31;

if Month(DateSerial(Year({?ME_Date}), Month({?ME_Date}), ClmnDay-1)) = 
    Month(DateSerial(Year({?ME_Date}), Month({?ME_Date}), ClmnDay)) or ClmnDay = 1
    Then ToText(DateSerial(Year({?ME_Date}), Month({?ME_Date}), ClmnDay),"MM/dd/yyyy")
    Else ""

If the column value formula works for you, please mark it as the solution so others can find it.

[mblocker1]

Thanks both worked perfectly!