none
Sharepoint Calculated column with [Today] RRS feed

  • Question

  • Hi,

    I am using Sharepoint 2007 Enterprise edition.

    I have two columns called Start Date and End Date (both of datetime type). These columns are used to maintain start date and end date of a contractor in a company. I have a calculated column (called Status) which displays "Active" or "InActive" depending on the below mentioned conditions.

    I used this formula :

    =IF(OR(OR(AND((Today>[Start Date]),[End Date]=0),AND((Today=[Start Date]),[End Date]=0)),OR(OR(AND((Today<[End Date]),(Today=[Start Date])),AND((Today<[End Date]),(Today>[Start Date]))),AND((Today=[Start Date]),(Today=[End Date])))),"Active","InActive")

    This is working but not updating the value correctly. I think Today value is not getting updated dynamically.

    I have 5 conditionds:

    1. Start Date < Today (Where End Date =0)

    or Start Date = Today where (End Date =0)

    or Start Date < Today < End Date

    or Start Date < Today and Today = End Date

    or Start Date < Today and  End Date > Today

    Any help is appreciated.

    Thanks in advance.

    Thursday, October 21, 2010 2:47 PM

Answers

All replies

  • So if i understand you correctly your looking for this as a calculated field:

    If ((Today > Startdate) && ((Today < EndDate) || (EndDate == 0)))

    =IF(AND([Today] > [Startdate], OR(([Today] < [Enddate]),([Enddate] = 0))), "Active", "InActive")

    (Im not to good at calculated columns ;)

    Regards
    Krister


    ziggstern.com
    • Proposed as answer by PSeeryOMNI Monday, November 11, 2013 12:53 PM
    Thursday, October 21, 2010 6:15 PM
  • Hey UmaS..

    Try this... Instead of taking the raw value of datatime.Today from 'Today', take it in an indirect way.

    1. Create another column in the list called Today and set it as a calculated column with formula as Today. This column can be hidden in views.

    2. Instead of using the word Today in your formula, use the column name [Today] which will actually contain the value of Today.

    Hope this helps!

    • Edited by Mike Walsh FIN Friday, November 26, 2010 2:07 PM Sig removed. Asks people to mark his post as answer
    Thursday, October 21, 2010 7:43 PM
  • Sridhar - the fake today trick doesn't work, check back on your list tomorrow.

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

    Friday, October 22, 2010 2:44 PM
  • check my blog for different date formulas and tricks.

    http://moonistahir.wordpress.com/category/sharepoint-2007/


    Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)
    • Marked as answer by Wayne Fan Wednesday, October 27, 2010 9:20 AM
    Friday, October 22, 2010 3:30 PM
  • Yes you are right, Today field is not updated until we edit the record. I may have to write a timer job to solve this issue.

    Thanks for your response. I appreciate it.

    Thursday, October 28, 2010 5:37 PM
  • Yes, Today logic will not work. I tried it the way Sridhar suggested. Today does not get updated dynamically every day. I may have to add some custom coding for this.

    But Thanks for all your responses.

    Thursday, October 28, 2010 5:40 PM
  • The best way of doing it is using a timer job services. Check it out in my blog

    http://manas-pradhan.blogspot.com/2011/12/using-today-calculation-in-sharepoint.html

    Tuesday, December 27, 2011 12:42 PM
  • I successfully used the NOW() function to calculate the date difference.  I am using 2010 version.

    Friday, May 17, 2013 6:47 PM
  • @ClairePoint

    Sorry but you just can't have used NOW() successfully - it does not work, never has.

    Are you either using the fake column trick (that doesn't update) or using a default value instead of a *calculated column*?


    Pentalogic Technology - Web Parts for Microsoft SharePoint www.pentalogic.net

    Monday, May 20, 2013 8:08 AM
  • Hi!

    This is best solution: http://sharepoint-2013-solutions.com/product/sharepoint-2013-today-field-updater-without-starting-workflow/

    I use this and is really great.

    Monday, April 21, 2014 2:27 PM
  • I used the today function like in excel

    (Today())

    Tuesday, October 28, 2014 4:30 PM
  • It's possible using SP.Services and some JS code.

    Just see my code snippet and change it to your requirements.

     // A $( document ).ready() block.
    $( document ).ready(function() {
     
     
    function dateCompare(time1,time2) {
      var t1 = new Date();
      var parts = time1.split(":");
      t1.setHours(parts[0],parts[1],parts[2],0);
      var t2 = new Date();
      parts = time2.split(":");
      t2.setHours(parts[0],parts[1],parts[2],0);
    
      // returns 1 if greater, -1 if less and 0 if the same
      if (t1.getTime()>t2.getTime()) return 1;
      if (t1.getTime()<t2.getTime()) return -1;
      return 0;
    }
    
    function dateDiff(time1,time2) {
    // can also be done by converting the time to seconds    
      var t1 = new Date();
      var parts = time1.split(":");
      t1.setHours(parts[0],parts[1],parts[2],0);
      var t2 = new Date();
      parts = time2.split(":");
      t2.setHours(parts[0],parts[1],parts[2],0);
    
      return parseInt(Math.abs(t1.getTime()-t2.getTime())/1000);
    }
    
    
     
    retrieveListItems();
    
    function retrieveListItems() {
    
       var siteUrl = _spPageContextInfo.webAbsoluteUrl;
       var fullUrl = siteUrl + "/_api/web/lists/GetByTitle('PGDueDate')/items?$filter=Title ne ''";
       
       
        $.ajax({
            url: fullUrl,
            type: "GET",
            headers: {
                "accept": "application/json;odata=verbose",
                "content-type": "application/json;odata=verbose",
            },
            success: onQuerySucceeded,
            error: onQueryFailed
        });
    }
    
    function onQuerySucceeded(data) {
    	
    var today = new Date();
    var dd = today.getDate();
    var mm = today.getMonth()+1; //January is 0!
    var yyyy = today.getFullYear();
    
    if(dd<10) {
        dd='0'+dd
    } 
    
    if(mm<10) {
        mm='0'+mm
    } 
    
    today = dd+'.'+mm+'.'+yyyy;
    
    
        $.each(data.d.results, function (key, value) {
    		
    		console.log(value.Title);
    		
    		var Beginn = moment(value.Beginn).lang("de").format("L");
    		var Ende = moment(value.Ende).lang("de").format("L");
    
    		
    		//console.log("Beginn "+Beginn);
    		//console.log("Ende "+Ende);
    		
    		//console.log("Aktuelles Datum "+today);
    		
    		var a = moment(today,'D.M.YYYY');
    
    		var b = moment(Ende,'D.M.YYYY');
    
    		var diffDays = a.diff(b, 'days');
    		console.log("Differenz in Tagen "+diffDays);
    		
    		
    		if (diffDays > 0){
    
    		console.log("ueberschritten");	
    		
    		
    $().SPServices.SPUpdateMultipleListItems({
    
    
    
      listName: "PGDueDate",
      
      
      CAMLQuery: "<Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+value.Title+"</Value></Eq></Where></Query>",
      
      
      valuepairs: [["Restlaufzeit", "abgelaufen"]]
      
      
    }); 
    		
    		
    		
    		} else{
    			
    				
    			
    			var resstring = diffDays.toString();
    			var newres = resstring.replace('-','');
    			var newres2 = newres+" Tage";
    			console.log("PG "+newres+" Tage");
    			
    			
    $().SPServices.SPUpdateMultipleListItems({
    
    
    
      listName: "PGDueDate",
      
      
      CAMLQuery: "<Query><Where><Eq><FieldRef Name='Title' /><Value Type='Text'>"+value.Title+"</Value></Eq></Where></Query>",
      
      
      valuepairs: [["Restlaufzeit", newres2]]
    
      
    }); 
    
    
    		}
              
      
             });
    		 		 
      // Set Ajax refresh context
      var evtAjax = {
        currentCtx: ctx,
        csrAjaxRefresh: true
      };
      // If set to false all list items will refresh
      ctx.skipNextAnimation = true;
      // Initiate Ajax Refresh on the list
      AJAXRefreshView(evtAjax, SP.UI.DialogResult.OK);
    
    
    }
    
    function onQueryFailed(sender, args) {
        alert('Error!');
    }
    
    });

    Tuesday, January 3, 2017 8:22 PM