none
Dynamically filter by Current Month

    Question

  • Is there a formula that I can use the determines the current month then uses that to calculate a Start and End date range. With a simple formula I can filter using 2 different fields "Start Date" and "End Date" entered by the user but can this be made to be more dynamic?

    I have a list of data staff enter and I am trying to create a view that can filter from the 1st to last day of the current month.
    Thursday, April 30, 2009 10:23 PM

Answers

  • Hi

     

    If you want to modify your sharepoint view to only show items created in the current month, you can create two calculated columns that returns "First Day of Month" and "Last Day of Month". And then add a filter on your view to create filtered view that shows only the current month's items. 

     

    In calculated fields you can use the following formulas to get first day of month and last day of month.

    First Day of Current Month Formula=DATE(YEAR([Created]), MONTH([Created]), 1) 

    Last Day of Current Month Formula =DATE(YEAR([Created]), MONTH([Created])+1,1)-1 

     

    Hope this helps

     

    Lu Zou

     

     

    • Marked as answer by Mike Walsh FIN Friday, November 27, 2009 1:57 PM
    Tuesday, May 05, 2009 2:56 AM
  • I've blogged about how you can setup filters in Views to show only items for the current calendar month,previous calendar month, current week etc.

    Its using the technique Lou Zu and others talked about with a little diagram to hopefully make it a little clearer.

    How To Use Filters in SharePoint to show items in the current Calendar Month
    Friday, November 27, 2009 11:36 AM

All replies

  • Hi,

    I don't whether i got your problem properly or not but i think you want to go to first data and last date of current month and want to know no of days in month.

      DateTime DateObj;
            int NoOfDate = 0;

        
                DateObj = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

                DateObj = DateObj.AddMonths(1);

                DateObj = DateObj.AddDays(-1);

                NoOfDate = DateObj.Day;
           
         DateTime lastDay=new DateTime(DateTime.Today.Year, DateTime.Today.Month, NoOfDate);

    HASRH
    Friday, May 01, 2009 7:40 AM
  • I was hoping to create a formula I can use in the Filtering portion of the view of a specific list, not sure that this is possible?

    I was thinking the same strategy, find the current month, then create 2 filters

    1. Start Date >= day1 of current month & year
    2. End Date <= day1 of next month & year -1day

    I am reviewing the SharePoint example formulas at http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx, thinking a combination of these might work? I just need to figure how to determine the current month.

    Harsh your example looks like VBscript, how do I use your code on the SP server for a specific list and view?

    Friday, May 01, 2009 5:48 PM
  • Hi

     

    If you want to modify your sharepoint view to only show items created in the current month, you can create two calculated columns that returns "First Day of Month" and "Last Day of Month". And then add a filter on your view to create filtered view that shows only the current month's items. 

     

    In calculated fields you can use the following formulas to get first day of month and last day of month.

    First Day of Current Month Formula=DATE(YEAR([Created]), MONTH([Created]), 1) 

    Last Day of Current Month Formula =DATE(YEAR([Created]), MONTH([Created])+1,1)-1 

     

    Hope this helps

     

    Lu Zou

     

     

    • Marked as answer by Mike Walsh FIN Friday, November 27, 2009 1:57 PM
    Tuesday, May 05, 2009 2:56 AM
  • That is very similar to the idea I came up with. I used the idea from another thread (http://social.technet.microsoft.com/Forums/en-US/sharepointcustomization/thread/9368bd9c-17fe-456d-a9ad-71a1ff6e6041) about creating a column called "today" since you can not use [Today] variable in the calculated column (only MS Gods know why).

    CurrMthStart=DATE(YEAR(Today),MONTH(Today),DAY((Today)-DAY(Today)+1))    - output date & time
    CurrMnthEnd=DATE(YEAR(CurrMnthStart),MONTH(CurrMnthStart)+1,DAY(CurrMnthStart))    - output date & time

    These 2 columns display the first day of the current month and the first day of the next month. But when creating the Filter on the view I get the following error msg.
    Filter value is not in supported date format.

    1. With my approach, I am using SPs date attributes to calcuate date & time why do I get a format issue?
    2. I like your version a bit better, looks cleaner and shorter but if we use the [Create Date] and someone misses time, creates enteries late or back dates them, some entries could wrongly be missed or encluded in the filter. Any ideas on how to avoid that?
    Tuesday, May 05, 2009 9:42 PM
  • The answer hit me after posting my 2nd question. I have taken your approach and modified as follows

    FirsttDay=DATE(YEAR([Start Date]),MONTH([Start Date]),1)
    LastDay=DATE(YEAR([Start Date]),MONTH([Start Date])+1,1)-1

    I created a filter as follows
    Start Date >= [FirstDay]

    I still get the error "Filter value is not in supported date format" what am I doing wrong?

    Once I get the above error corrected I'll add another line to the filter
    Finish Date <= [LastDay]
    • Edited by Stunpals Tuesday, May 05, 2009 9:56 PM added comments
    Tuesday, May 05, 2009 9:53 PM
  • Lu
    After testing this I realized my formula will not work. The [FirstDay] and [LastDay] columns show the first and last day of the month based on the value of  the [Start Date] column but the [FirstDay] and [LastDay] values will always be true when comparing to the [Start Date] & [End Date], so no filter is applied.

    I can not believe this can be so difficult!

    Even looking at your example what would the Filter formula be? I can't see how to calculate this without the [Today] variable as its the only way to get perspective on actual current day. But again MS has restricted this for some reason.
    Wednesday, May 06, 2009 5:56 PM
  • Check this workaround posted on Chris Johnson's blog, http://blogs.msdn.com/cjohnson/archive/2006/03/16/552314.aspx

    Hope this helps.
    Friday, May 08, 2009 4:48 PM
  • I have tested this option by cjohnson and it works for 1 day only. As soon as the next day comes the values do not change, unless I am missing something?

    Has anyone been able to get the [Today] workaround to work continually once setup, day after day not just 1 day.


    Wednesday, May 20, 2009 3:19 PM
  • I have setup a more basic test to check the [today] in a calc column. This will be used for a job tracking list, when the work is rec'd a new item will be added to the list with the Rec'd date filled in.

    Columns
    Received Date
    Complete Date
    Days Active

    I will try to calc [Days Active] for all jobs that do not have a empty [Complete Date] field with the [today] as described in the link above from cjohnson otherwise it will use [Completed Date]-[Received Date]) to calc.

                   =IF([Completed Date]="",today-[Received Date],[Completed Date]-[Received Date])    - output data type is number


    I tested this yesterday and it did display the correct number of days for uncompleted jobs. The next day the values remained the same as the prev day. I recreated the [today] column to see if the calc days would update and now I get a value of -39934.0, when I delete the newly created [today] column the formula works but I am sure it will only be for 1 day again?

    Do I have to create/delete at [today] column every day for this to work?
    • Edited by Stunpals Wednesday, May 20, 2009 3:52 PM
    Wednesday, May 20, 2009 3:41 PM
  • Is it possible to use the filter on a view to calculate the month value of the builtin [Today] value.

    I tried =MONTH([Today]) but it doesnt allow this?
    Wednesday, May 20, 2009 5:25 PM
  • I've blogged about how you can setup filters in Views to show only items for the current calendar month,previous calendar month, current week etc.

    Its using the technique Lou Zu and others talked about with a little diagram to hopefully make it a little clearer.

    How To Use Filters in SharePoint to show items in the current Calendar Month
    Friday, November 27, 2009 11:36 AM