none
Get data for last 12 hours RRS feed

  • Question

  • Hi, I have a custom list with one of the column as a date column. I need to create a view that will list the data for the last 12 hours. The date column will have the time. How can I do this? Calculated column is not helping me out, am not sure what is wrong.

    Cutloo

    • Moved by Mike Walsh FIN Monday, March 14, 2011 7:40 AM admin q not programming (From:SharePoint - Development and Programming (pre-SharePoint 2010))
    Monday, March 14, 2011 6:40 AM

Answers

All replies

  • What is the formula you used in the calculated field?

    Try using the following formula to find the hours between two date/times field

    =TEXT(Column2-Column1,"h")

    now create a view to display when the value is more than 12. Please let us know if it works.


    --Cheers
    • Edited by Mike Walsh FIN Monday, March 14, 2011 6:54 AM Prasath. These are not private conversions so please let US know not please let ME know
    Monday, March 14, 2011 6:47 AM
  • I dont have 2 columns.

    My formula will be

    =TEXT(DateTimecolumn - 12,"h") . Is this correct?

    I need to show last 12 hours data.

    Monday, March 14, 2011 6:57 AM
  • No, you should replace 12 with current time using [Today], and the output will the in hours, then check if the hours is less than or equal to 12.

    Refer the following blog for more information

    http://blogs.msdn.com/b/sharepointdesigner/archive/2008/08/01/date-functions-in-calculated-fields.aspx


    --Cheers
    Monday, March 14, 2011 7:01 AM
  • It says "calculated columns cannot contain volatile functions like Today and Me"
    Monday, March 14, 2011 7:22 AM
  • I created a dummy calculated column called "MyToday" (=[TODAY]) but looks like unless all the items are updated the column will be empty. Now since this column is empty am getting incorrect data in the calculated column

    =TEXT(DateTimecolumn - [MyToday],"h")

    Help needed.

    Monday, March 14, 2011 7:56 AM
  • Hi Cutloo,

     

    you should create column Today and then create calculated column where you want you use function Today. Instead of function today, insert column today. 

    Last step is to delete column today and then, your formula should work as expected.

     

    Enjoy!


    Robi MCT Kompas Xnet d.o.o. Ljubljana, blog: http://xblogs.kompas-xnet.si, website: http://www.kompas-xnet.si, Slovenia
    Monday, March 14, 2011 8:48 AM
  • Its giving me wrong data.

    DateTimecolumn is having data like 6/19/2008 3:00 PM and the calculated column is showing 9 hours which means there is only 9 hrs of difference between today's date and the DateTimecolumn ?

    Monday, March 14, 2011 9:02 AM
  • I was curious if [TODAY] column that I created is having the correct value.

    #1. I created a column called Today,made it single line of text

    #2. Created another column "ShowToday" ,made it calculated column and gave formula as =[Today]

    #3.Deleted the column created in step #1

    #4. Now when I see the list and checked the values in ShowToday all the rows have the same value "40,616".

    Dont understand what is happening.

    Monday, March 14, 2011 9:12 AM
  • "ShowToday" column type should be DateTime.

     


    --Cheers

    Monday, March 14, 2011 9:47 AM
  • Yeah it is DateTime.

    I just displayed the difference between the ShowToday column and Today in hours by muliplying it by 24

    DateTimecolumn - [ShowToday ] * 24. The calculation is not accurate because Today takes only till 12:00 AM hence there is a difference of few hours which makes it incorrect.

     

    Monday, March 14, 2011 10:09 AM
  • you should be using the following formula to find the hours between the columns

    =TEXT(DateTimecolumn - [ShowToday],"h")


    --Cheers
    Monday, March 14, 2011 10:14 AM
  • TEXT(DateTimecolumn - [ShowToday],"h") is same as TEXT(DateTimecolumn - [Today],"h"), correct?
    Monday, March 14, 2011 10:16 AM
  • Yeah, since the today is returing only the date and not time, its hard to filter it based on time.

    Can you try this using SharePoint Designer? refer to the following URL

    http://nickgrattan.wordpress.com/2008/04/24/filtering-views-by-time-and-date/


    --Cheers
    • Marked as answer by Cutloo Wednesday, March 16, 2011 5:04 AM
    Monday, March 14, 2011 10:40 AM
  • @R0ber70 - the 'fake today column trick doesn't work unless you update every single one of your items every single day.

    Or in this case since he's filtering on last 12 hours you will have to update everything very hour.

    http://blog.pentalogic.net/2008/11/truth-about-using-today-in-calculated-columns/


    Pentalogic Technology - Web Parts for Microsoft SharePoint www.pentalogic.net
    Monday, March 14, 2011 2:22 PM
  • I thought I'll open the  AllItems.aspx page in SharePoint designer tool, right click the list and convert to xslt view.

    Now I have the column that has the calculated formula =[Today] which is supposed to show the current time. Since this is taking 12 00 AM by default can I have a xslt function that will display the current time in this column?

    Hope I am clear..

    Tuesday, March 15, 2011 7:06 AM