locked
filter a list by the number of the month of today. RRS feed

  • Question

  • Hello,

    I have a list on I want to filter it by the number of the month. The list I present by Pivot and every month I need to change the filter list manually depending on the number of the month. Is there an automated solution? Formula of the month of the day can be used in filtering the list? Thank you

    Tuesday, March 22, 2011 1:35 PM

Answers

  • Hi Yuval,

    Sorry for my misunderstanding.

    You can try to create a data view web part or convert the list form to data view web part by right click list form in SharePoint designer and convert it to XSLT data view. Then add filter to the data view with following script.

    [@month field=ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'MM')]

    ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'MM') will return current month, but for the moth small than 10, it will return 01,02... with 0 before current month, so you need to do some modifications of the calculate formula to get a month format witon a 0 if the month is smaller than 10, please try the following formula.

    =IF(MONTH(date column)<10,"0"&MONTH(date column),MONTH(date column))

    Best regards,
    Emir

    • Proposed as answer by techChirag Sunday, March 27, 2011 8:54 PM
    • Marked as answer by Yuval C Tuesday, March 29, 2011 10:02 AM
    Sunday, March 27, 2011 10:16 AM

All replies

  • You need to create a calculated column to achieve this, refer to the following blog for more info

    http://guru-web.blogspot.com/2006/02/some-of-my-favourite-date-wss-list.html


    --Cheers
    Wednesday, March 23, 2011 11:24 AM
  • Hi Yuval,

    Do you want to get current month?

    If so, first you need to create a today column follow these steps:
    1. Create a new column and named it today. (Which type is OK, because we will delete this column later)
    2. Create a calculate column and add formula: =[today]. Select date and Time type return from the formula.
    3. Delete today column.
    Now, today's date is displayed in calculate column.
    4. Add a new calculate column with single line of text type, add formalr: =MONTH(the name of the first calculate column).
    Current month will return in this new calculate column.

    Let me know if there are any other questions, thanks.

    Best regards,
    Emir

     


    Thursday, March 24, 2011 3:13 AM
  • Hi Emir Liu and thanks for the reply,


    I know how to create a calculated column that displays the current month.
    The problem is I need to filter in the current month and the beginning of the month need to manually change the filter on the display.
    For example, on 01/04/11 I need to change the filter in a month=4.
    I'm looking for an automatic solution that I have a lot of views should filter them again.

    Thank you.


    Sunday, March 27, 2011 7:02 AM
  • Hi Yuval,

    Sorry for my misunderstanding.

    You can try to create a data view web part or convert the list form to data view web part by right click list form in SharePoint designer and convert it to XSLT data view. Then add filter to the data view with following script.

    [@month field=ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'MM')]

    ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'MM') will return current month, but for the moth small than 10, it will return 01,02... with 0 before current month, so you need to do some modifications of the calculate formula to get a month format witon a 0 if the month is smaller than 10, please try the following formula.

    =IF(MONTH(date column)<10,"0"&MONTH(date column),MONTH(date column))

    Best regards,
    Emir

    • Proposed as answer by techChirag Sunday, March 27, 2011 8:54 PM
    • Marked as answer by Yuval C Tuesday, March 29, 2011 10:02 AM
    Sunday, March 27, 2011 10:16 AM
  • Thank you very much.
    If I want to filter by previous month formula is:

    [@month field=ddwrt:FormatDateTime(string(ddwrt:Today()),1033,'MM')-1]

     

    Thank you.


    Tuesday, March 29, 2011 10:04 AM
  • My list is very large 120,000 rows and I get an error at the point of view to XSLT conversion. Screenshot attached. I would appreciate your help.

    http://rotter.name/User_files/nor/4d932c1b6afe1bbf.png



    Wednesday, March 30, 2011 1:12 PM
  • Hi Yuval,

    This is a new question, please give a new thread in the future if you have a new question. Thanks for your understanding.

    Here is a similar thread, hope it could help.

    http://social.msdn.microsoft.com/Forums/en-US/sharepointcustomization/thread/f4d6a086-4a18-4dae-b12e-026ee1a3d9c4

    Best regards,
    Emir

    Thursday, March 31, 2011 2:23 AM