none
SharePoint View - 3pm yesterday to 3pm Today - Need Help

    Question

  • Hello All, 

    I have a basic requirement to show "Daily" stats on a list.  Criteria are 3pm yesterday to 3pm today due to hard cut off submissions.  

    In researching, I took action by modifying a view on (Created Date) & custom calculated column that only shows hours of created date in 24-hour format.  I thought I could apply a simple filter:

    Created date <= [Today]-1 & [Custom Colum] <= 15 & Created date >= [Today] & [Custom colum] >= 15 

    Turns out it doesn't like it.   Any other simple suggestions that I can apply or research to get 3pm yesterday - 3pm today filter?  

    I have limited SPD experience in writing code but eager to learn.

    Thank you for your time, any help would be appreciated

    Friday, February 27, 2015 4:56 AM

Answers

  • Hello,

    The code I shared that was tested code for Calendar List. Please verify You may be doing anything wrong.

    - Yes you need to use SPD CAML editing, because the browser based filter allow 'All'  match , and we need 'Either-Or' Filters. So you will have to modify the view CAML using SPD.

    - The code I have shared in my previous reply that was for Calendar list/view.I am Sharing the Tested and verified steps again for Custom List/Tabular View and for Modified Field . Hope This will be helpful.

    1- Create a Calculated column in your list. Note that should return Numeric Value for Hours , Not "hh:mm:ss" . So the formula should be  -

    • Name - ModifiedHrs
    • Type - Calculated
    • Formula:     =HOUR(Modified)
    • Format -Number (1, 1.0, 100)
      Number of decimal places:  0

    2- Create a Tabular View ( e.g. DayState.aspx) for your List. (not Calendar View), through browser with filter ( Modified equal to [Today]).

    3. Now Open the view page (DayState.aspx) in SharePoint designer code view [Edit in Advance Mode]

    4. Locate the CAML query associated with the filter (Modified = [Today]), you can find something like

    <WebPartPages:WebPartZone ---
    <WebPartPages:XsltListViewWebPart ----
    -----
    <XmlDefinition>
    <View --->
    <Query>
    <OrderBy>--</OrderBy>
    <Where>
    <Eq>
    <FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value>
    </Eq>
    </Where>
    </Query>
    <ViewFields---->
    ----
    </View></XmlDefinition>
    </XmlDefinition>
    


    5. Replace the <Where>----</Where> Section with this one

    <Where>
    <Or><And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today OffsetDays="-1"/>
    </Value></Eq><Gt><FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">14</Value></Gt></And>
    <And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value></Eq><Lt>
    <FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">16</Value></Lt></And></Or>
    </Where>

    6. Save the page , and test it in browser

    7. Note :

    • If you have different field names , then Please change the field names with InternalName of fields in your List
    • Set the CAML in single line , remove spaces
    • This is for Custom List/Tabular View. For Calendar view , the View Definition are in different format, you will have to replace < and > as I explained in my previous post.
    • This is tested code , so it should work. In Case of any issue , you can PM/email me or Post here.Thanks


    Thanks
    Ganesh Jat [My Blog | LinkedIn | Twitter ]
    Please click 'Mark As Answer' if a post solves your problem or 'Vote As Helpful' if it was useful.

    • Marked as answer by DarkenSky Monday, March 02, 2015 6:43 PM
    Saturday, February 28, 2015 7:05 AM

All replies

  • Hi,

    Hope the below blogs help you

    http://www.thorntontechnical.com/tech/sharepoint/sharepoint-list-views-filtered-by-date-and-time#.VPALX3yUfy4


    Please remember to click 'Mark as Answer' on the answer if it helps you

    Friday, February 27, 2015 6:43 AM
  • Please try this -

    - Create a Calculated column "CreatedTimeHour" in your list , and save created date hours in 24hr format

    - Create a new view "MyView" for this list through browser with filter ( created equal to [Today])

    - Now Open the view page (MyView.aspx) in Sharepoint designer in code view

    - Locate the CAML query associated with the filter , you can find something like

    &lt;Where&gt; ........... &lt;/Where&gt;

    - Delete this query (from "&lt;Where&gt;"  to &lt;/Where&gt;)

    - In-place of this query write a new CAML like this - (Please replace all < with &lt; and all > with &gt;)

    <Query>
    	<Where>
    		<Or>
    			<And>
    				<Eq>
    					<FieldRef Name="Created"/>
    					<Value Type="DateTime">
    					<Today/>-1
    					</Value>
    				</Eq>
    				<Gt>
    					<FieldRef Name="CreatedTimeHour"/>
    					<Value Type="Int">
    					14
    					</Value>
    				</Gt>
    			</And>
    			<And>
    				<Eq>
    					<FieldRef Name="Created"/>
    					<Value Type="DateTime">
    					<Today/>
    					</Value>
    				</Eq>
    				<Lt>
    					<FieldRef Name="CreatedTimeHour"/>
    					<Value Type="Int">
    					16
    					</Value>
    				</Lt>
    			</And>
    		</Or>
    	</Where>
    </Query>

    - Save the code and test the view in browser

    - Hope this will help



    Thanks
    Ganesh Jat [My Blog | LinkedIn | Twitter ]
    Please click 'Mark As Answer' if a post solves your problem or 'Vote As Helpful' if it was useful.

    • Proposed as answer by taylor.l Friday, February 27, 2015 8:09 AM
    Friday, February 27, 2015 7:16 AM
  • Hello,

    Thank you for the suggestion but I can not run powershell scripts, do not have access to the server. I have site collection admin rights.   With Powershell ou have to run it on the server the farm is located on.

    Friday, February 27, 2015 2:27 PM
  • Hello,

    Thank you for the quick reply.   So I tried your suggestion but the values are coming back are way to high.  I also decided to use "Modified" not created.  Here is the code I replaced in SPD (screen imaged below). 

    In addition error code when I click on the "Daily View" in browser:

    Error Message:

      One or more field types are not installed properly. Go to the list settings page to delete these fields.
      Correlation ID:e25a4981-4a04-44c6-8aea-49bd01c2eca9

     

    Any other suggestion that I can do to make this work is apperciated. 

    Thank you

    Code Updated:

    Friday, February 27, 2015 2:38 PM
  • In addition: 

    Calculated column - "MetricsTime_Mod" (TEXT setting) Formula: 

    =TEXT(Modified,"hh:mm:ss")


    • Edited by DarkenSky Friday, February 27, 2015 3:01 PM
    Friday, February 27, 2015 2:56 PM
  • I have updated the view based on Ganesh response:

    I believe the data is correct now.   I'm going to validate later today.  Let me know if you still think I should do the coding in CAML.

    Friday, February 27, 2015 3:50 PM
  • Validation failed:  I'm still not getting all the data I should be able to see.  

    Any suggestions on next steps would be helpful. 

    Thanks!

    Friday, February 27, 2015 6:30 PM
  • Hello,

    The code I shared that was tested code for Calendar List. Please verify You may be doing anything wrong.

    - Yes you need to use SPD CAML editing, because the browser based filter allow 'All'  match , and we need 'Either-Or' Filters. So you will have to modify the view CAML using SPD.

    - The code I have shared in my previous reply that was for Calendar list/view.I am Sharing the Tested and verified steps again for Custom List/Tabular View and for Modified Field . Hope This will be helpful.

    1- Create a Calculated column in your list. Note that should return Numeric Value for Hours , Not "hh:mm:ss" . So the formula should be  -

    • Name - ModifiedHrs
    • Type - Calculated
    • Formula:     =HOUR(Modified)
    • Format -Number (1, 1.0, 100)
      Number of decimal places:  0

    2- Create a Tabular View ( e.g. DayState.aspx) for your List. (not Calendar View), through browser with filter ( Modified equal to [Today]).

    3. Now Open the view page (DayState.aspx) in SharePoint designer code view [Edit in Advance Mode]

    4. Locate the CAML query associated with the filter (Modified = [Today]), you can find something like

    <WebPartPages:WebPartZone ---
    <WebPartPages:XsltListViewWebPart ----
    -----
    <XmlDefinition>
    <View --->
    <Query>
    <OrderBy>--</OrderBy>
    <Where>
    <Eq>
    <FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value>
    </Eq>
    </Where>
    </Query>
    <ViewFields---->
    ----
    </View></XmlDefinition>
    </XmlDefinition>
    


    5. Replace the <Where>----</Where> Section with this one

    <Where>
    <Or><And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today OffsetDays="-1"/>
    </Value></Eq><Gt><FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">14</Value></Gt></And>
    <And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value></Eq><Lt>
    <FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">16</Value></Lt></And></Or>
    </Where>

    6. Save the page , and test it in browser

    7. Note :

    • If you have different field names , then Please change the field names with InternalName of fields in your List
    • Set the CAML in single line , remove spaces
    • This is for Custom List/Tabular View. For Calendar view , the View Definition are in different format, you will have to replace < and > as I explained in my previous post.
    • This is tested code , so it should work. In Case of any issue , you can PM/email me or Post here.Thanks


    Thanks
    Ganesh Jat [My Blog | LinkedIn | Twitter ]
    Please click 'Mark As Answer' if a post solves your problem or 'Vote As Helpful' if it was useful.

    • Marked as answer by DarkenSky Monday, March 02, 2015 6:43 PM
    Saturday, February 28, 2015 7:05 AM
  • This is a totally different approach for highighting which items were published in a given timewindow.


    You can not use [Today] in Calculated Columns because Caculated Columns are only updated on Item creation or Change. So if the Item is not updated [Today] will be yesterdays date tomorrow.

    You can however trick SharePoint to execute HTML (and thus Javascript) in a Calculated Column by setting the datatype to Number instead of Single line of text. This only works in Views!

    See http://www.ViewMaster365.com/#/How for details.

    Then you can do the today comparison with Client side JavaScript

    see formula below:

    The blank image triggers the onload function (for every item in the View)

    Because SharePoint counts dates from 12/31/1899 and Javascript start at 1/1/1970 we substract 25560 days

    note the server offset.. JavaScript date on the client is different on my system because of timezone differences with the server

    the date is calculated to produce 3 values

    1=within timewindow

    2=before time window

    3=after todays timewindow

    because the function executes on the blank image we know which TR can be highlighted 

    if you want to hide rows change the color>3 to color>1 and rows NOT in the window will be hidden.

    Paste this Formula in a Calculated Column "Published" and set the datatype to Number.

    ="<img src=/_layouts/images/blank.gif onload=""{"
    &"var hour=60*60*1000;"
    &"var cutoffdate=new Date();cutoffdate.setHours(15,00,00,0);"
    &"var timegate=1*24*hour;"
    &"var serveroffset=-1;"
    &"var itemdate=new Date(serveroffset*hour+24*hour*"
    &Modified-25569
    &");"
    &"var colors=['none','lightgreen','lightcoral','lightgoldenrodyellow'];"
    &"var color=cutoffdate-itemdate > timegate ? 2 : itemdate > cutoffdate ? 3 : 1;"
    &"var TR=this.parentNode.parentNode.parentNode;"
    &"TR.style.backgroundColor=colors[color];"
    &"if(color>3){TR.style.display=colors[0];}"
    &"}"">"
    &TEXT(Modified,"hh:mm:ss")

    HTH

    Danny Engelman

    ViewMaster365.com




    Saturday, February 28, 2015 8:32 PM
  • Thank you Ganesh.

    I'll try this tomorrow and get back with validation.  Once confirmed i'll mark as answer & helpful.   Again thank you for the time. 

    Saturday, February 28, 2015 9:12 PM
  • This is a totally different approach for highighting which items were published in a given timewindow.


    You can not use [Today] in Calculated Columns because Caculated Columns are only updated on Item creation or Change. So if the Item is not updated [Today] will be yesterdays date tomorrow.

    You can however trick SharePoint to execute HTML (and thus Javascript) in a Calculated Column by setting the datatype to Number instead of Single line of text. This only works in Views!

    See http://www.ViewMaster365.com/#/How for details.

    Then you can do the today comparison with Client side JavaScript

    see formula below:

    The blank image triggers the onload function (for every item in the View)

    Because SharePoint counts dates from 12/31/1899 and Javascript start at 1/1/1970 we substract 25560 days

    note the server offset.. JavaScript date on the client is different on my system because of timezone differences with the server

    the date is calculated to produce 3 values

    1=within timewindow

    2=before time window

    3=after todays timewindow

    because the function executes on the blank image we know which TR can be highlighted 

    if you want to hide rows change the color>3 to color>1 and rows NOT in the window will be hidden.

    Paste this Formula in a Calculated Column "Published" and set the datatype to Number.

    ="<img src=/_layouts/images/blank.gif onload=""{"
    &"var hour=60*60*1000;"
    &"var cutoffdate=new Date();cutoffdate.setHours(15,00,00,0);"
    &"var timegate=1*24*hour;"
    &"var serveroffset=-1;"
    &"var itemdate=new Date(serveroffset*hour+24*hour*"
    &Modified-25569
    &");"
    &"var colors=['none','lightgreen','lightcoral','lightgoldenrodyellow'];"
    &"var color=cutoffdate-itemdate > timegate ? 2 : itemdate > cutoffdate ? 3 : 1;"
    &"var TR=this.parentNode.parentNode.parentNode;"
    &"TR.style.backgroundColor=colors[color];"
    &"if(color>3){TR.style.display=colors[0];}"
    &"}"">"
    &TEXT(Modified,"hh:mm:ss")

    HTH

    Danny Engelman

    ViewMaster365.com




    Hello Danny, 

    Thank you for the alternative approach but using Javascript on the client side is sometimes unreliable.  I have seen cases as well that it will slow down performance of the view (depending on <g class="gr_ gr_615 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="615" id="615">amount</g> of data).  <g class="gr_ gr_658 gr-alert gr_gramm Punctuation only-ins replaceWithoutSep" data-gr-id="658" id="658">Also</g> no one is using [Today] in calculated formula.  I'm aware of the trick but as mentioned on other forums and seen personally it <g class="gr_ gr_817 gr-alert gr_spell ContextualSpelling multiReplace" data-gr-id="817" id="817">isn't to</g> reliable.  

    Ganesh approach using CAML query and adjusting the VIEW in SPD is the logical approach.   I believe this will work and will be testing soon.   

    Thanks


     

    Saturday, February 28, 2015 9:23 PM
  • Hello,

    The code I shared that was tested code for Calendar List. Please verify You may be doing anything wrong.

    - Yes you need to use SPD CAML editing, because the browser based filter allow 'All'  match , and we need 'Either-Or' Filters. So you will have to modify the view CAML using SPD.

    - The code I have shared in my previous reply that was for Calendar list/view.I am Sharing the Tested and verified steps again for Custom List/Tabular View and for Modified Field . Hope This will be helpful.

    1- Create a Calculated column in your list. Note that should return Numeric Value for Hours , Not "hh:mm:ss" . So the formula should be  -

    • Name - ModifiedHrs
    • Type - Calculated
    • Formula:     =HOUR(Modified)
    • Format -Number (1, 1.0, 100)
      Number of decimal places:  0

    2- Create a Tabular View ( e.g. DayState.aspx) for your List. (not Calendar View), through browser with filter ( Modified equal to [Today]).

    3. Now Open the view page (DayState.aspx) in SharePoint designer code view [Edit in Advance Mode]

    4. Locate the CAML query associated with the filter (Modified = [Today]), you can find something like

    <WebPartPages:WebPartZone ---
    <WebPartPages:XsltListViewWebPart ----
    -----
    <XmlDefinition>
    <View --->
    <Query>
    <OrderBy>--</OrderBy>
    <Where>
    <Eq>
    <FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value>
    </Eq>
    </Where>
    </Query>
    <ViewFields---->
    ----
    </View></XmlDefinition>
    </XmlDefinition>


    5. Replace the <Where>----</Where> Section with this one

    <Where>
    <Or><And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today OffsetDays="-1"/>
    </Value></Eq><Gt><FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">14</Value></Gt></And>
    <And><Eq><FieldRef Name="Modified"/>
    <Value Type="DateTime"><Today/></Value></Eq><Lt>
    <FieldRef Name="ModifiedHrs"/>
    <Value Type="Number">16</Value></Lt></And></Or>
    </Where>

    6. Save the page , and test it in browser

    7. Note :

    • If you have different field names , then Please change the field names with InternalName of fields in your List
    • Set the CAML in single line , remove spaces
    • This is for Custom List/Tabular View. For Calendar view , the View Definition are in different format, you will have to replace < and > as I explained in my previous post.
    • This is tested code , so it should work. In Case of any issue , you can PM/email me or Post here.Thanks


    Thanks
    Ganesh Jat [My Blog | LinkedIn | Twitter ]
    Please click 'Mark As Answer' if a post solves your problem or 'Vote As Helpful' if it was useful.

    Hello Ganesh,

    Had time today and updated.  Something was off (not related) on my old view and so decided to start from scratch.  

    Thank you for your help & very helpful workthrough post.   This is working 100%. 

    I have two more ideas that i'm working on, would you mind if I reach out to you?  

    Monday, March 02, 2015 6:43 PM
  • Great it works for you.Thanks for the feedback.

    Yes please , you can contact me through forum or email me.



    Thanks
    Ganesh Jat [My Blog | LinkedIn | Twitter ]
    Please click 'Mark As Answer' if a post solves your problem or 'Vote As Helpful' if it was useful.

    Tuesday, March 03, 2015 3:22 AM