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

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.

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:

Hi,

Hope the below blogs help you

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

In addition: 

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

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

• Edited by DarkenSky 20 hours 40 minutes ago

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

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.

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!

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