How do I filter by [Today] and time in SharePoint list view ?

Question

I'm trying to filter Sharepoint lists based on date and time. But It only works with date, ignores the time in data and time field.

Claustral

Answer 1

In SharePoint Designer I edit the CAML query from my view in Advanced Mode. And I add IncludeTimeValue="True" in CAML tag Value Type="DateTime". I'm filtering my results by time.

           <Query>
                <OrderBy>
                    <FieldRef Name="Modified" Ascending="FALSE"/>
                </OrderBy>
                <Where>
                    <Or>
                        <Gt>
                            <FieldRef Name="Start"/>
                            <Value Type="DateTime"  IncludeTimeValue="True">
                                <Today/>
                            </Value>
                        </Gt>
                        <Gt>
                            <FieldRef Name="TimeOver"/>
                            <Value Type="DateTime"  IncludeTimeValue="True">
                                <Today/>
                            </Value>
                        </Gt>
                    </Or>
                </Where>
            </Query>

Claustral

Great hack man! This should be OOB. I think you meant Caml, this syntax makes me anything but calm lol – Prospectus

Answer 2

First, SharePoint filtering using the [Today] wildcard only compares dates, not times. To my knowledge there is no web interface way of comparing times.

Assuming "TimeOver" is a your project deadline and "Start" is when the project begins...

Add something to a list when it is overdue by saying: TimeOver is Less than [Today]

Add something to a list when it was started today: Start is equal to [Today]

Add something that was created in the past week: Start is greater than [Today]-7

Add something to a list that is due within 30 days: TimeOver is greater than [Today] AND TimeOver is less than [Today]+30

Niblick

@Tabares: mind blown. im going back and reworking so many solutions based on this. Thanks! – Niblick

Answer 3

Create a calculated column in NUMBER format (e.g. call it 'CreatNum'). The value of the column is the date field (e.g. =Created). Then filter by that field, as in CreateNum field is greater than 46,885.3313 (a date in number value). The same calculated column will convert existing date fields into numbers so you can easily tell what number value to filter on.

Papilla