SharePoint TODAY field and calculated fields...
A customer sent me a link to a page they found about using the Today field in calculated sharepoint fields. I don't know how you would fear with our support people ... but here is the note from Pete Blair on the workaround:
It is possible to use "Today" in a function as a reference to today’s date
(despite what SharePoint tells you). There is a very simple work around that
doesn't involve very much effort or complexity.
First, create a new column in your list with the Column Name of "Today".
Click "OK". (It doesn't matter what type of column or data it is, this is
just a place holder).
Next, create a column with the data where you would like to use "Today" as a
reference to today’s date. The column type should be "Calculated". In the
formula field, create your formula using "Today" as if it held the current
date/time.
SharePoint will calculate the formula based on the assumption that you will
be using "Today" as a reference to the new column you just created.
Next, edit the new column named "Today" that you created in the first step.
On the very bottom of the page, click "Delete", to delete the column. In
your formula, SharePoint will keep the reference to "Today" but it will
change from referencing your column, to a reference to the current date.
This can be used in any of the Date and Time functions, but I haven't tested
it beyond that.
Notes:
If you want to edit the formula containing "Today" SharePoint will not let
you, and will give you the same error about not using Today or Me in a
function. To get passed this problem you have to temporarily create another
Today column.
The same functionality will also work with Me.
Comments
Anonymous
March 15, 2006
Some additional information here: http://www.sharepointblogs.com/mkruger/archive/2005/03/18/1500.aspxAnonymous
March 15, 2006
Hallo zusammen,
oft wurde diese Frage schon gestellt, bis jetzt wußte leider keiner eine Antwort. Chris...Anonymous
March 16, 2006
I have been using this trick for quite a while and it has been really useful. I first spotted this on Mark Kruger's blog http://www.sharepointblogs.com/mkruger/archive/2005/03/18/1500.aspx.
The drawback is "the formula will not calculate all list items on refresh but rather by the addition and deletion of a new Today column or by editing each of the list items individually. However, you can code this to make it more dynamic..."Anonymous
March 20, 2006
Nice workaround - I am hoping that filter formulas are seriously addressed in v3. But, in the mean time, this trick got me a gold star from the office manager when I fixed her admin subsite. And we all know that office managers secretly run the world.
Neil Bailey
training specialist / web monkey / it wonderboyAnonymous
April 11, 2006
This is all very well and may (or may not) work correctly - however, this is completely useless from a support point of view as the next person to edit the list wont know how [Today] has been faked so may inadvertently break this formula or try and replicate it (not knowing about this trick).Anonymous
April 24, 2006
This workaround works fine for new lists however I have a list already containing hundres of list items. The following solution works much better for me:
Create a new calculated field (FilterDate) and use the original Date field as the input source and then add the formula. In my case, I used "= [Document Date] + 31" as the formula. Set the output type to date. Then I set my filter up so that FilterDate >= [Today]
Enjoy!Anonymous
December 26, 2006
Hi, it is a great tip. for some reason - when i'm using it now in sharepoint V3 - it seems that the calculated column which is based on Today doesn't show the righ value (it seems the calculation use the value of the day in which i created the column or the item as Today, and is not changed dynamically...) is it so? v-kesar@microsoft.comAnonymous
January 18, 2007
@keren sar-el: as rachel said higher up in these comments: "The drawback is "the formula will not calculate all list items on refresh but rather by the addition and deletion of a new Today column or by editing each of the list items individually. However, you can code this to make it more dynamic..." March 16, 2006 12:09 PM "Anonymous
April 27, 2007
Now that we all know all the way to the point the calculated field does not refesh automatically, and "needed to code it to make it moredynamically..." now, it stops here... Any solution to make it more dynamic???? I am sure a lot of people would like to find out!Anonymous
May 29, 2007
Hi there, I also can't find any explanation anywhere of the coding required to make refreshing more dynamic... I'm beginning to think that there is some inside joke about something really obvious :-) Please, please tell us how?Anonymous
June 12, 2007
Hi, Can someone post the workaround again? This blog site has been shut down! Many thanx, BrendenAnonymous
July 28, 2007
The comment has been removedAnonymous
August 03, 2007
This trick no longer works. I just tested it with the most recently install of wss v3.Anonymous
August 03, 2007
The comment has been removedAnonymous
August 03, 2007
This little piece of code worked fine for calculating a running today of days... =IF([Completion Date]-[Start Date]<0,"0",IF([Completion Date]-[Start Date]=0,"1",[Completion Date]-[Start Date]))Anonymous
August 06, 2007
Any updates about making the calculated field to refresh automatically? Thanks a lot for the advice. Regards, MariaAnonymous
August 20, 2007
I second Maria's request. I'm displaying a different image depending on the date difference from TODAY. The logic works fine, but it only evaluates if I open a list item and save it (I don't make any edits). It seems that the calculation is not applied when you open the list but only when each record is "edited". Any help would be appreciated!Anonymous
August 27, 2007
I tried the above fix, the one submitted by chris johnson and it worked fine for me. The dates dont remain static, i added it last friday when the date was the 24/08/2007, and i've checked it today 28/08/2007 and its actually changed the date. Great help, thank youAnonymous
August 30, 2007
Sharepoint Services 2003 - Workaround for filtering on today's dateAnonymous
August 31, 2007
Sharepoint Services 2003 - Workaround for filtering on today's dateAnonymous
September 25, 2007
It didn't work for me. I've tested it with sharepoint v3 and it doesn't work. The date remains static... Any other idea?Anonymous
October 04, 2007
it worked for me, but I'm getting negative days for recent posting, ie today-created = -ve. I guess this is because today is defaulting to 12:00 or 00:00. I'll check tomorrow, when this should filter out.Anonymous
October 21, 2007
The comment has been removedAnonymous
November 05, 2007
No scary experiences using this workaround. When MS does not want us to use Today/Me in calculated column, there should be a reason for it??Anonymous
November 26, 2007
I have used the above method to get todays date fine, but what if I want to display date and time not just the date (much like the MySQL function NOW())? 'Today' function seems to only get the date. I am using the formula =IF([is completed?], TODAY) with the column returning a date AND time in the format dd-mm-yyyy 0:00 where dd-mm-yyyy is today's date but 0:00 is not the time the field was calculated. I want 0:00 to reflect the time the field was calculated. Any help? Seems like Microsoft makes this process stupidly difficult!Anonymous
January 24, 2008
I have tried this workaround and it appears to work. However when you export to EXCEL 2003, the field with the reference to "Today" displays "=#REF!". When I try to manually update the cell I get a message that the column is "read only" and cannot be modified. Any further suggestions?Anonymous
March 05, 2008
The comment has been removedAnonymous
March 12, 2008
I have tried the above method, however the date of today appears to be 30/12/1899, any ideas? We are on WSS 3.0Anonymous
March 16, 2008
In other language versions you should also use Today and Me names for columns although Sharepoint UI suggests using local names for these functions!Anonymous
March 24, 2008
With WSS v3.0, I was using SPD workflow to get around this issue. (note, this may not work for you!) Basically, you design a workflow that is "always looping".
- Add a Number column called "heartbeat"
- Create a workflow that fires when an item is changed (and put in a way to fire it once).
- All this workflow does is wait 8 hours, and then add +1 to the "heartbeat" column value (which kicks off another copy of the workflow) The catch? WSS v3.0 SP1 introduced a new bug which causes SPD workflows which fire "on edit" to fail. There is also a new problem with the "Wait until..." SPD activity. So, you will see one of 2 problems:
- The "looping" workflow will "complete" after a certain # of loops. (ie - it never fires off after the heartbeat edit)
- You will be "Waiting" forever MS tells me this will be fixed around SP2 timeframe... we will see.
Anonymous
April 14, 2008
Just tried it with WSS v2.0 and it works fine for me. It even refreshes.Anonymous
May 22, 2008
Regarding Mike Thomas' illustration above that includes the part: Column 3 – This final column is a Yes/No column, call it "IsCurrentWeek." Based on your Start Time entered of your Calendar Event. It does a comparison of the previous Column “TodayPlus.” If your event StartTime is less than seven days from the current day, then it will return a “Yes” value. =[Start Time] < TodayPlus Where do you enter the formula in a yes/no value column?????? Please help if you know. ThanksAnonymous
June 17, 2008
The comment has been removedAnonymous
June 17, 2008
The comment has been removedAnonymous
July 16, 2008
Seems that you guys are making this harder than it has to be. I have SPS 2003. To view the past 30/60/90 days, this is what I do: Create a column called view. Calculated field, date only. The formula should be: Created+90 or Modified+90 or create your own date field called Date; Date+90 Then set the filter to: view >= [Today] That seems to do the trick for me.Anonymous
July 21, 2008
Though the workaround does work the first time, a month later I found the calendar does not update the date of "Today" by itself. It always treat the date on which you create/update the columns (TodayPlus90, TodayPlus0.. etc) as "Today". If SharePoint has a system-reserved variable that represents system's real current date, probably the workaround can be a solution. Otherwise, it seems not a good one. :-( Any idea from anyone?Anonymous
August 16, 2008
On my blog I show an easier way to achieve the same result as this “Today” trick: http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/Anonymous
August 23, 2008
More about the "Today" trick on my blog: http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/Anonymous
August 26, 2008
I have posted on my blog more details about this "Today" trick: http://pathtosharepoint.wordpress.com/2008/08/14/calculated-columns-the-useless-today-trick/ Also a workaround using the Data View Web Part and JavaScript: http://pathtosharepoint.wordpress.com/2008/08/25/a-countdown-for-tasks-lists/Anonymous
August 27, 2008
I am a SharePoint novice, and I'm using WSS 3.0. I have to come up with a way to only show ONLY the current month's events in the summary view of the calendar web part. I have tried both examples that Mike Lin and Mike Thomas have posted and neither one seems to work for me. This is an example of what my output looks like when using Mike Thomas' example: 9/1/2008 12:00 AM Labor Day IsCurrentMonth: Yes TodayPlus: 9/27/2008 9/18/2008 12:00 AM Birthday One IsCurrentMonth: Yes TodayPlus: 9/27/2008 2/1/2010 12:00 AM Birthday Two IsCurrentMonth: Yes TodayPlus: 9/27/2008 2/3/2010 12:00 AM Birthday Three IsCurrentMonth: Yes TodayPlus: 9/27/2008 I opted to show the IsCurrentMonth and TodayPlus fields in the sumamry view so that I could verify what value was being returned. Does it matter that my events do not have a 'Start Time' because they are All Day Events? Can anyone help me out with this? It's literally driving me insane! ;) Thanks!Anonymous
October 05, 2008
Hi all, I am using [Today] as a calculated field in one of the column. My requirement is I have to list the items Which are due in this month and which are due in next month automatically. i have used the confition Month[Today] to list the items for the current month & MONTH(Today)+1=MONTH([DepartureDate]--> is to list the next month items. But every month i have to create [Today] column and delete it so to work this properly. Is there any work around for this? Please let me know asap with best regards, BenkiAnonymous
October 07, 2008
I am setting up a calendar and I want a list of events for today by the room. I would like to either add a sort/filter/group to the Current event function already in SharePoint. Is there a way to do this? I have found some calcs for showing the event for today but I have many recurring items. These do not show the correct date in the Current Eventy view. They show the first date of the recurring item. (Begin date)Anonymous
October 14, 2008
Hi benki I am not able to use Month[Today].... in my calcukated column(formula)can i know what the problem will be.Anonymous
October 15, 2008
I created a column Birthday in calenderlist and the type of information column given as "calculated" And the formula i given as this =IF((DATE(MONTH(Today),DAY(Today))==(DATE(MONTH(DOB),DAY(DOB))) and the data type returned from this formula as Yes/No But am getting the error is ""The formula contains a syntax error or is not supported. Troubleshoot issues with Windows SharePoint Services"" is this formula is right???Anonymous
November 11, 2008
Im using the following Round((([END TIME] - [START TIME])*24),1) to work out the duration of an appointment in a calculated field within a calendar list. All works well until the user makes the appointmet a reacuring meeting then the furmula works out the entire duration. How can i break this down or divide by the number of occurancesAnonymous
December 03, 2008
The comment has been removedAnonymous
December 16, 2008
The work around works fine at first until the next day. Is there a way to get the deata to "refresh" automatically so that the function continues to work? Thanks!Anonymous
December 18, 2008
In my workflow i need to send a mail with date value (19/12/2008),but it displays like 19/12/2008 12.00AM. even while creating itself,i created this DATE column as date only in my list . how to display this date without time? Plz help me.Anonymous
March 02, 2009
I know this doesn't have to deal with the TODAY function, however it is a calculated column with the date format (date only, not time). I am trying to use this field as the "End Date" in a calendar view of the list, however, I keep getting an error. Can somebody take a look at my formula and tell me what I am doing wrong? =IF(AND(Approval_Status="Approved",[Target Release Date]>0),[Target Release Date],IF(AND(Approval_Status="Pending",[HRSM Desired Release Date]>0),[HRSM Desired Release Date],"")) *Note: Target Release Date and HRSM Desired Release Date are both "Date" columns in my list. I use the "" if Approval Status isn't equal to "Approved" or "Pending," so the Targeted (this formula's column) Release Date equals nothing instead of the goofy 1899 date SharePoint defaults to.Anonymous
March 18, 2009
A common question I get and SharePoint challenge is to show the age of something in SharePoint. A typicalAnonymous
April 20, 2009
You can use the same trick to use the ID of a Lookup Column in a calculated Column.Anonymous
April 28, 2009
Thanks a lot you save me a lot of timeAnonymous
June 09, 2009
PingBack from http://cellulitecreamsite.info/story.php?id=655Anonymous
June 16, 2009
PingBack from http://topalternativedating.info/story.php?id=3583Anonymous
July 17, 2009
Hey guys, I came up with a solution that will get you the difference between a date (with time also) and correctly take care of some problems I saw in other solutions (for instance having less than a day in hours difference). Hope you will find it useful! =IF(HOUR([Ending Date])>HOUR([Starting Date]),DATEDIF([Starting Date],[Ending Date],”d”)&” days “&HOUR([Ending Date]-[Starting Date])&” hours “,(DATEDIF([Starting Date],[Ending Date],”d”)-1)&” days “& HOUR([Ending Date]-[Starting Date])&” hours “)&MINUTE([Ending Date]-[Starting Date])&” minutes”Anonymous
July 30, 2009
Super this saved a boat load of time and effort.Anonymous
September 22, 2009
The comment has been removedAnonymous
September 22, 2009
Does anyone know if this has been addressed with SharePoint 2010?Anonymous
October 11, 2009
I've tested it with sharepoint v3 and it doesn't work. The date remains static... Any other idea? thanksAnonymous
December 13, 2009
Possible mid way....It's not perfect, but saves a LOT of time for me. Use the TODAY workaround, and when you want to update ALL records, simply recreate the a today column, and then delete it again. Seems to work for me and since I use this list once a week for a meeting, this is no major extra work. Hope this helps!Anonymous
January 26, 2010
That worked for me. Thank you!Anonymous
April 22, 2010
The comment has been removedAnonymous
May 14, 2010
In spanish Today=Hoy, in sharepoint calculated (sapnish) column =hoy work fineAnonymous
June 03, 2010
Is there anyway to have sharepoint send out emails when an end date is approching?