1,804 articles and 14,446 comments as of Monday, January 3rd, 2011

EndUserSharePoint has combined resources with NothingButSharePoint.com. You can now find End User (Mark Miller), Developer (Jeremy Thake) and IT Pro SharePoint (Joel Oleson) content all in one place!

This site is a historical archive and is no longer being updated. Please update your favorites, bookmarks and RSS feeds.

Friday, January 23, 2009

JQuery for Everyone: Replacing [Today]

Comments: 50

The [Today] thing gives people fits. It just doesn’t work in calculated columns like you want it to.

Here’s a slick way around it using some jQuery. To test this example for yourself, place the code sample in a Content Editor Web Part on the same page as a standard Discussion List view.

Two things to note here:

This script can dynamically find the correct column to calculate when you give it a name.
If your column is not called “Last Updated” you need to change the str parameter below.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function() {
	var str = "Last Updated"; //change this based on col header
	var today = new Date();
	today = Date.parse(today)/1000;
	var a=0;
	var headers = $("table.ms-listviewtable:first> tbody> tr:first th").get();
	$.each(headers, function(i,e){
		x = $(e).contents().find("a[title*='"+str+"']").length;
		a = x > 0 && i > a ? i : a;
	});
	var dArray = $("table.ms-listviewtable:first> tbody> tr:gt(0)").find(">td:eq("+a+")").get()
	$.each(dArray, function(i,e){
		var d1 = Date.parse($(e).text())/1000;
		var dd = (today-d1)/86400;
		var dh = (dd-Math.floor(dd))*24;
		var dm = (dh-Math.floor(dh))*60;
		var time = ((Math.floor(dd) > 0 ? Math.floor(dd) +" days, " : "")+
					(Math.floor(dh) > 0 ? Math.floor(dh)+" hrs, " : "")+
					(Math.floor(dm)+" min"));
		$(e).text(time);
	});
});
</script>

Paul Grenier

View all entries in this series: PaulGrenier-JQuery for Everyone»

Entries in this series:

I recommend SharePoint resources daily on my twitter account here.

Posted by AutoSponge on Friday, January 23, 2009 at 7:00 am
Filed under Tips and Tricks · Tagged with

Please Join the Discussion

50 Responses to “JQuery for Everyone: Replacing [Today]”

Charlie Epes says:

January 23, 2009 at 9:22 am

Hi Paul:
This is very cool… sets my head spinning thinking of other ways to incorporate or tweak it for other list date fields.

This is a slight departure from other time tweaks since refreshing the entire (F5) web page changes ALL the [Last Updated] entries, not just the one that was modified.

Hmmm….

Charlie Epes

Reply
AutoSponge says:

January 23, 2009 at 7:33 pm

@Charlie,

Thanks for pointing out the mistake. I updated the script, it should be “a = x > 0 && i > a ? i : a;” not “a = x > 0 && i > a ? i : 0;”.

It’s still designed to replace a column. Targeting another column introduces a lot more logic which just hurts my head.

~Paul

Reply
Iain Munro says:

January 23, 2009 at 9:50 pm

Hi Paul

Thanks again for some great code.

I want to check this out for other projects I have been struggling with, but get NaN Min in the column.

I tried the new column as text and date with both the same result – Any Ideas?

Iain

Reply
AutoSponge says:

January 23, 2009 at 10:05 pm

@Iain,

The column should be Date & Time. But I think your issue may be something I forgot to mention for other lists.

This script searches for the Title of the header’s link, so for Modified, the Title=”Sort by Modified”.

Only on headers without sorting will it appear normally.

I think it’s a problem with the $= operator I’ve noticed in jQuery 1.3. So, you can either change the title to “Sort by Modified” or change the operator to *= in line 10.

Both ways should work.

Reply
AutoSponge says:

January 23, 2009 at 10:07 pm

I think I’m going to change the operator in my script so fewer people have issues.

The rest of the logic holds (but was not explained in the article for brevity). If you have two columns with the word ‘Modified’ the changes will only apply to the last one (farthest to the right).

Reply
Iain Munro says:

January 24, 2009 at 3:53 pm

Hi Paul

I think I have done what you suggested, but nothing has changed.

Iain

Reply
Brad says:

February 12, 2009 at 5:51 pm

Dear Readers,

How are you guys? Thanks for this code BUT I have a little issue I would like to correct.

I would like to know how I could plug this code into the Desired Field in a list. Only thing it seems to change is the title. (Which I know is what you wanted to change, but I was considering to change the title of an internal list name?)

Let me see if I can explain this.

I have a List.

8 Different Properties ( Housing Property 1,2,3,4,5,etc )

Each of these display on 1 page with a +/- and inside there is basically like a issue log.

I need to know how I could populate a Today or Last Update down in the list, and not at the title.

If I can get the function for the list to give me at least todays date, I can write the code for the rest.

http://www.myocdns.com/Screen_Sharepoint.png

The following above is a picture of what I am saying, the blue is the area I need to fill.

Thanks,
Brad

Reply
Wanda says:

February 12, 2009 at 6:05 pm

Hi Paul, I would like to show “days since last updated” for a task list. This would be today-modified expressed in days.
I used the clunky fake-Today column, but I don’t like having to update that everyday.
I would like to use this script, but it isn’t working for me. I have copied the jquery to a library on my parent site, and changed the script scr to reference this. (e.g.: /sites/xx/scripts/jquery.min.js)

I changed the str to =”Modified”;

what else do i need to do?

Reply
AutoSponge says:

February 12, 2009 at 6:39 pm

@Wanda,

Since we’re matching with a wildcard, we always take the last column header with “Modified.” Make sure your “Modified” column is the last one (left to right) appearing with that word.

If you only want to see the time in days, change the last variable line to this:
```
var time = (Math.floor(dd) > 0 ? Math.floor(dd) +" days" : "Today");
```
Let me know how it goes.

Reply
AutoSponge says:

February 12, 2009 at 8:40 pm

@Brad,

If you’re trying to put Today() in a column so you can do calculations on it–well, that’s the problem–you can’t. That’s why I introduced this “hack.”

By putting the relevant data on the screen (in this case Last Modified), we can use the client’s current time (basically Today) to make some calculations and update the screen’s display–WE’RE NOT CHANGING LIST DATA. Which is what you need to do calculations in calculated columns and the like.

I hope that answers your question–even if it’s not the answer you were hoping for.

Reply
Wanda says:

February 12, 2009 at 11:17 pm

Hi Paul

no luck tonight. I’ll try again tomorrow. thanks.

Reply
Brad says:

February 13, 2009 at 2:01 pm

Hey thanks. I found something that helps me out a little more then this hack.

http://www.sharepointblogs.com/dez/archive/2008/07/31/dynamically-updating-a-sharepoint-calculated-column-containing-a-quot-today-quot-reference.aspx

I just compiled the Source and run the scheduled task nightly. It uses a external XML file that you can set up and assign to a task list of your needs.

Just set up the -Today- Function like the typical hacking way and use this to keep it going nightly.

I enjoyed this code, but I needed something that helps the client out using the basic sharepoint commands so they don’t need to come back to me every 5 minutes and ask ” how do you do -this- with the code setup on the sharepoint site.”

I appreciate your time and energy, but as they say if there is a “WILL” there is a “WAY” :]

@wanda
It seems you are trying to do something like I am, just not wording it the same way. This might help you out in the way you are seeking.

P.S.To All: If you don’t have a copy of Visual Studio I will be more than happy to email or post the code in a zip (Compiled and non-compiled) so you can plug it into your site.

Thanks,
- Brad

Reply
- Steve says:
  
  May 25, 2010 at 12:45 pm
  
  Brad,
  
  Did you ever upload the console application up somewhere?
  
  I would like to get the code as I do not have visual studio.
  
  Thanks,
  -Steve
  
  Reply
  - Ceyda says:
    
    September 2, 2010 at 6:18 am
    
    Hi Brad
    
    Is it possible for you to share the code please?
    
    Is there a website we can get it from?
    
    Thank you
Jorgen says:

February 17, 2009 at 4:00 pm

Hi Paul,

Regarding this code, I’ve got the same problem as Iain – NaN min occurs in the “last updated” col. Will the language of the installation have any impact on this code (my SP installation is in Norwegian) or date/time formatting?

PS: I’ve tried out some of your jQuery snippets, and I really like them! I find the EUSP site perfect for my non-excisting programming level (working hard for SP super star level)!

Reply
AutoSponge says:

February 17, 2009 at 7:19 pm

@Jorgen,

You asked some great questions. I have no idea what Norwegian looks like to my code, but here are some ideas for troubleshooting:

1. NaN means ‘not a number’ so chances are the script found a column with character data or a date format it did not recognize. First check that whatever you set “var str” to is the LAST column (left-to-right) with those exact words. For instance if you have var str = ‘Modified’; then the columns Modified and Modified By, Modified By will try to apply the script.

2. I use Date.parse($(e).text()) to turn a formatted date into an epoch date. If the local date format is not recognized by JavaScript, you may need to add a custom function to parse the date.

Reply
Lance says:

March 18, 2009 at 12:29 pm

What if I want to apply this to a Data View Web Part on the page? I have tried giving my DVWP a table ID and then changing table.ms-listviewtable to #nameoftable but this then seems to only change the first column item in the table.

Any thoughts on how to do this?

Reply
Kat says:

April 30, 2009 at 3:13 pm

I can’t recall who blogged about this originally, but there is a quick workaround for using [today] in a calculated column. Simply create a column in your list, and call it “Today”. Then set your calculated column up and use [today] the way you really want to. Once you have everything else set up and finalized, you can delete your “Today” column. Voila! The calculated column (so long as you don’t go back in to edit it) now works and is referencing the real “today”. It’s a nice trick for anyone who wants to do this quickly and who doesn’t plan to often/ever change any settings for that particular calculated column.

Sorry I can’t remember the name of the blogger who suggested this originally. I didn’t create this workaround- I just use it all the time.

Reply
Christophe says:

April 30, 2009 at 7:32 pm

Kat: many bloggers published the trick you are talking about, and it just doesn’t work.
More details here:
http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

Reply
Marc says:

June 3, 2009 at 7:33 am

Hi Paul, thanks for sharing this script. It works great if you are in the same time zone as where the SharePoint server is located. I want to use this to determine the age of a document since it was created.

This script compares the created date of the SharePoint doc (American time in my case) to my local PC time (Central European time). So as a default age I get 6 hours. Somebody in Singapore will even get a higher number. If I change my PC to the same time zone as the SharePoint server then it shows the correct age. Is there a way to compare the server time instead of the local pc time?

Many thanks,

Marc

Reply
AutoSponge says:

June 3, 2009 at 7:22 pm

Marc,

You have to adjust both the client’s time and the server’s representation to GMT. For the client, try this:
```
var today = new Date();
var clOff = today.getTimezoneOffset();
today = Date.parse(today)/1000;
today = today-clOff;
```
Then for the server (hard-coded to the server’s timezone), try:
```
var d1 = Date.parse($(e).text())/1000;
var svrOff = 6*60; //GMT offset in minutes
d1 = d1-svrOff;
```
Reply
Christophe says:

June 3, 2009 at 9:50 pm

Marc, this is “by design” as JavaScript will run on your PC.
If you need the same time reference for everybody, you’d better pick the current time on the server side. You could for example use a Data View Web Part and xsl timestamp functions for that.

Reply
Carlos says:

July 20, 2009 at 9:35 am

HEllo EVERYONE.
I just copy the code, change the search column and the code is almost working fine, but the count it’s made is not correctly.
I use a column that has the following: “18/07/2009 18:16″ and it should show the difference between this date and the now time, that is 20/07/2009 11:34″ but it is showing just 17 hrs, 16 min. It should be 1 day 17 hrs, 16 min. What is wrong? thanks

Reply
Maxx says:

October 22, 2009 at 1:56 am

Hi everyone

Regarding this code, I’ve got the same problem as Iain and Jorgen – NaN min occurs in the “last updated” col. I did everything you said but still got same result. I wonder if Jorgen or Iain have solved their problem? Maybe you guys could share your wisdom if you solved the mistery? How to fix this “Nan min” showing in the last column.

Reply
Maxx says:

October 22, 2009 at 3:15 am

Hi again, I just checked if time format could adress my problem and it did, seems like time format 24hours is not gonna work with this script, as soon as I changed to 12 hour format it started working, I was wondering if there is some workaround for this problem?

Reply
AutoSponge says:

October 28, 2009 at 8:27 am

@Maxx,

Try changing this line:

var d1 = Date.parse($(e).text())/1000;

to this:

var d1 = UTC.parse($(e).text())/1000;

Reply
Rob Chisholm says:

October 28, 2009 at 10:44 am

It only works if there is no grouping being used? I’m trying to show how long projects have been open. It works fine until I group by Project Status. Any ideas? Thank you.

Reply
AutoSponge says:

October 28, 2009 at 11:48 am

@Rob,

It still works but it will only total for individual OPEN groups, not the entire list.

Reply
David Petersen says:

November 3, 2009 at 4:33 pm

I’ve been trying to do the same thing and came across your code. The problem I have is that I am trying to create an “aging” field that shows how many days have expired since a particular date. The date field is called [StartDate] and the calculated field [Aging] should reflect the result of a formula Today – [StartDate].

Has anyone done any work towards this? I would assume that you would find the value of the [StartDate] for each item in your list and then calculate the aging field similar to your above code. Any ideas?

Reply
David Petersen says:

November 3, 2009 at 5:39 pm

I solved my own problem. I made the [Aging] field a DateTime calculated field =[StartDate]. Then I used the javascript code to modify the [Aging] field. I modified the javascript so that it would only calculate Days since that is all I needed. The result is that my Aging field now reflects the number of days from [StartDate].

Reply
Adam says:

December 9, 2009 at 10:54 am

Found a great solution to this problem in MOSS 2007 –

1. Create a new column called ‘Today’. Make it a single line of text.

2. Create a new calculated field called ‘Aging’. Give it a formula of ‘DATEDIF([Modified],[Today],”D”)’. Have it be a number with 0 decimals.

3. Go to the edit page of the ‘Today’ column and delete it.

See here for the reference:
http://blogs.officezealot.com/mauro/archive/2008/06/04/21062.aspx

Just did this on our Fab 40 Helpdesk template for service requests, and it works GREAT!!

Hope this helps, and requires NO coding.

Reply
David says:

December 9, 2009 at 12:09 pm

Adam,
There are many posts online that explain why the [Today] trick is useless. Someone on this discussion list aleady pointed to one such post…

http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/

Just use “modified” for the same behavior…

Reply
- EndUserSharePoint says:
  
  December 9, 2009 at 12:10 pm
  
  David — I wasn’t going to say anything…
  
  Reply
Caroline says:

December 11, 2009 at 12:07 pm

Hi Paul,

I’m having trouble finding the code you posted, it doesn’t look like there is a “Read More” to click anywhere.

Please let me know if you can re-post it or if I’m missing it.

Thanks,
Caroline

Reply
Vijay says:

March 30, 2010 at 1:55 am

Hi,

I’m making a mess when trying to update this code so that it works for us folk in the UK (dd/mm/yyyy).

Please can someone help update the code so it is compatible for the UK?

Cheers!

Reply
Hank says:

July 6, 2010 at 11:38 am

I am using this script to calculate the duration of a web application outage. I’ve created a calculated field and the calculation is “=START_TIME”.

How can the script be edited to execute the script only on items with no value in the “END_TIME” column?

But would like it to calculate the duration between START_TIME and END_TIME, if the END_TIME exists.

Thanks,

Hank

Reply
Ryan says:

July 7, 2010 at 11:30 am

Hi Paul, thanks for the great tutorial. I was able to make it work to show the number of days since the date given in another column. However, the script doesn’t work quite right if I add grouping to my list. Basically, the calculation still works, but it gets displayed in the wrong column. I’m grouping by two other columns, and the calculation gets displayed one column left of where it should be displayed.

Any suggestions?

Reply
Chris says:

July 20, 2010 at 4:09 pm

This is great help. Glad I found this.

I created a Calculated Column called Last Modified, set it to =[Modified], set the “The data type returned from this formula is:” to ‘Date and Time’ and “Date and Time Format:” to ‘Date & Time’.

In the script I change line 4 to var str = “Sort by Last Modified”;

It shows perfectly the correct time. If you have current list items and they show “Nam min” it is more then likely you have not enter in a Date and Time. BUT, setting up a calculated column and pointing it to Modified fixes that problem.

Reply
Jonathan says:

August 17, 2010 at 4:42 pm

Hello, I realize this is an old post, but was hoping for some help or guidance. I have added the discussion as a web part on a page. How can I tweak the script to update the Last Updated field? Is this possible?

Reply
Shawn White says:

August 27, 2010 at 5:09 pm

This is great! I am still relativally new to working with JavaScript and more so with JQuery. I have gotten this work just fine with calculating the “Modified” column; however, I’d like to have it caculated based upon the “Created” column. This way, I can view the “age” of a list item.

I am unsure what needs to change in this script. Can someone enlighten me?

Thanks!

Reply
- Shawn White says:
  
  August 27, 2010 at 8:29 pm
  
  I figured it out! Just change “var a=0;” to “var a=1;”. I should have figured this out before even posting this. Doh!
  
  Reply
  - Shawn White says:
    
    September 1, 2010 at 1:56 pm
    
    Nope…I thought that would do the trick but it didn’t. Any ideas anyone?
- Caroline says:
  
  September 1, 2010 at 2:19 pm
  
  Hi Shawn,
  
  I have created a column called “today” that defaults to today’s date when the item is created. Then this is the code I have for days outstanding that includes some tweaks to Paul’s code.
  
  I hope this helps!
  
  $(function() {
  
  var str = “Days Outstanding”; //change this based on col header
  
  var today = new Date();
  
  today = Date.parse(today)/1000;
  
  var a=0;
  
  var headers = $(”table.ms-listviewtable:first> tbody> tr:first th”).get();
  
  $.each(headers, function(i,e){
  
  x = $(e).contents().find(”a[title*='"+str+"']“).length;
  
  a = x > 0 && i > a ? i : a;
  
  });
  
  var dArray = $(”table.ms-listviewtable:first> tbody> tr:gt(0)”).find(”>td:eq(”+a+”)”).get()
  
  $.each(dArray, function(i,e){
  
  var d1 = Date.parse($(e).text())/1000;
  
  var dd = (today-d1)/86400;
  
  var dh = (dd-Math.floor(dd))*24;
  
  var dm = (dh-Math.floor(dh))*60;
  
  var time =(Math.floor(dd) > 0 ? Math.floor(dd) +” ” : “Today”);
  
  $(e).text(time);
  
  });
  
  });
  
  Reply
  - Caroline says:
    
    September 1, 2010 at 2:21 pm
    
    Sorry, all the code wasn’t copied. Also “days outstanding” is my column name.
    
    $(function() {
    
    var str = “Days Outstanding”; //change this based on col header
    
    var today = new Date();
    
    today = Date.parse(today)/1000;
    
    var a=0;
    
    var headers = $(”table.ms-listviewtable:first> tbody> tr:first th”).get();
    
    $.each(headers, function(i,e){
    
    x = $(e).contents().find(”a[title*='"+str+"']“).length;
    
    a = x > 0 && i > a ? i : a;
    
    });
    
    var dArray = $(”table.ms-listviewtable:first> tbody> tr:gt(0)”).find(”>td:eq(”+a+”)”).get()
    
    $.each(dArray, function(i,e){
    
    var d1 = Date.parse($(e).text())/1000;
    
    var dd = (today-d1)/86400;
    
    var dh = (dd-Math.floor(dd))*24;
    
    var dm = (dh-Math.floor(dh))*60;
    
    var time =(Math.floor(dd) > 0 ? Math.floor(dd) +” ” : “Today”);
    
    $(e).text(time);
    
    });
    
    });
AutoSponge says:

September 1, 2010 at 3:20 pm

@Shawn,

I don’t really maintain this script anymore, but the comment in line 4 should have given you the clue :P

var str = “Last Updated”; //change this based on col header

Change it to the column header you want to use. The script counts how many cells in that header is then uses that column for the rest of the table body to work with the date info.

Reply
Shawn says:

October 18, 2010 at 3:52 pm

So…I eventually got this working for a few months. Then we upgraded to SP2010 and now I get the infamous “NaNmin” text in the column. The only way I have been able to correct this is by putting my column, “Age” in the farest left column. When I do, it calculates and displays fine. I am still digging into this…

Reply
Anvesh says:

December 30, 2010 at 6:38 am

Thanks a lot, Paul. You helped me solve my problem.

Reply

Trackbacks

Check out what others are saying about this post...

Fear and Loathing says:

October 9, 2009 at 11:18 am

Calculated Time Left columns in SharePoint with jQuery…

A current project I’m working on in SharePoint is an online auction. I’ll post more info about this and…

Reply
SharePoint MVP Blogs says:

October 11, 2009 at 3:47 pm

Calculated Time Left columns in SharePoint with jQuery…

A current project I’m working on in SharePoint is an online auction. I’ll post more info…

Reply
Dealing with the [Today] problem in SharePoint Calculated Fields « What’s The (Share)Point? says:

November 3, 2009 at 6:03 pm

[...] He has written a series entitled jQuery for Everyone and one of his articles was on Replacing [Today]. In his article, Paul talks about replacing a DateTime field with an Aging calculation. His [...]

Reply