Date Functions in Calculated Fields

re: Date Functions in Calculated Fields

Mindaugas — Mon, 04 Aug 2008 10:36:37 GMT

Just tried to create Today’s Date column and turn my calendar one day forward. Today’s Date value left the same, so it isn't pointing to real today. What are you doing here is the same as to use default Created column value because Calculated value is calculated just once when the item is created. I think there is a simpler solution to this. You could create Calculated colum like this NextMonth=DATE(YEAR([Created]), MONTH([Created])+1, 1) and then in your view filter use condition NextMonth > [Today] what means that you need to shows those items where today is still less then the first day of the next month (from item's Created column value).

re: Date Functions in Calculated Fields

wellick — Mon, 04 Aug 2008 17:39:59 GMT

I wonder if Amber (author of this post) even bothered to test this "recipe" before posting it to an official SPD blog.

Just as Mndaugas said, this doesn't work. [Today] is only calculated on item creation and edit, not every time list item is accessed.

Now, please, whoever posts to this blog - check your solutions before posting them. Or at least google them up. Filtering by [Today] and why it doesn't work has been discussed a hell lot of times already.

re: Date Functions in Calculated Fields

Stefan — Tue, 05 Aug 2008 09:48:32 GMT

The solution posted is not working. I fully agree with wellick's comment!

re: Date Functions in Calculated Fields

will — Wed, 06 Aug 2008 09:24:00 GMT

As previously mentioned filtering using [today] one a default view will not work. If you want to filter using [today] you will need to use SPD and create a data view you can then use xslt in the filter and do it.

Unfortunatly the version of xslt that ships with Sharepoint 2007 is very limited and can be a pain so it is often better to mix calculated columns and xslt instead of doing it all in xslt.

re: Date Functions in Calculated Fields

TobZ — Thu, 07 Aug 2008 08:29:16 GMT

Then Wellick, you should be able to solve the problem.

This is a good tip for people starting out with calculated values etc, the meaning isn't to provide full-scale solutions. If this doesn't suit your needs, you are free to customize this solution and do whatever changes you'd like.

Instead of picking on people who provide FREE support and assistance, you should perhaps show some appreciation.

Cheers

re: Date Functions in Calculated Fields

Ant Cee — Fri, 08 Aug 2008 20:20:27 GMT

Amber- you suc!! Seriously, check the web, this solution doesn't address the core problem of being unable to create a [Today] calculated column that dynamically updates the current date on a daily basis. Maybe Amber should talk to Bill G. about why MS bugs need to be discovered & flushed out by his customers rather than his programmers??

re: Date Functions in Calculated Fields

Abahl — Sat, 09 Aug 2008 01:17:41 GMT

Thanks for the feedback guys. I have updated the post to work around the problem that you guys are hitting.

Hope this helps.

Thanks,

Amber

SharePoint Designer Tipps

Michael Greth MVP SharePoint Blog — Tue, 12 Aug 2008 13:37:35 GMT

Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

SharePoint Designer Tipps

Mirrored Blogs — Tue, 12 Aug 2008 14:33:11 GMT

Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

SharePoint Designer Tipps

Michael Greth MVP SharePoint Blog — Tue, 12 Aug 2008 16:58:52 GMT

Hier habe ich ein paar Tipps zum SharePoint Designer zusammengestellt: Use SharePoint Designer to Email

re: Date Functions in Calculated Fields

Adam — Wed, 13 Aug 2008 20:17:32 GMT

Did you guys fix the problem? I'm still trying but the propsed solution doesn't work...

@Amber: why are you screen shots different from your writing??? E.G. you say in step 1 'Taday' but on your screen shot there's 'Today's date'

That's mess...

re: Date Functions in Calculated Fields

Christophe — Thu, 14 Aug 2008 18:23:13 GMT

In my opinion the “Today” column is useless, I have explained it here:

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

SPPD102 SharePointPodcast

Michael Greth MVP SharePoint Blog — Thu, 14 Aug 2008 23:07:45 GMT

Direkter Download: SPPD-102-2008-08-14 Aktuell Feedback Usergruppen ISPA International SharePoint Professionals

SPPD102 SharePointPodcast

Mirrored Blogs — Thu, 14 Aug 2008 23:38:09 GMT

Direkter Download: SPPD-102-2008-08-14 Aktuell Feedback Usergruppen ISPA International SharePoint Professionals

WSS 3.0 & MOSS: Recopilatorio de enlaces interesantes (XX)!

Blog del CIIN — Tue, 19 Aug 2008 23:29:01 GMT

Pues nada, dicen que las tradiciones están para mantenerlas en el tiempo y eso es lo que espero hacer

re: Date Functions in Calculated Fields

Adaickalavan P — Thu, 21 Aug 2008 17:37:13 GMT

All the workarounds fail in one or the other way. So, Microsoft have to add 'today' option for calculation. That would be the only solution.

re: Date Functions in Calculated Fields

Sherice — Wed, 01 Oct 2008 16:47:54 GMT

Adding the [Today] column worked great for what I was trying to do. I am using it to track the progress of projects, and I simply want to know if a project is On Schedule or Late for a report I need for my boss. The work around worked fine for this purpose.

re: Date Functions in Calculated Fields

Killab — Thu, 04 Dec 2008 02:50:31 GMT

You need to create a text only column and Title it "Today." Create your calculated column and reference the today. Once the Today column is deleted Sarepoint treats it as a "Today" variable.

re: Date Functions in Calculated Fields

CodeManWA — Tue, 09 Dec 2008 21:15:04 GMT

Worked great for me. Thanks Amber:)

re: Date Functions in Calculated Fields

Nate — Sat, 27 Dec 2008 00:18:28 GMT

I've done this by running a designer workflow to touch each item each day, but its a PITA. MS really dropped the ball on date filtering for lists in sharepoint. This is something that really needs addressed if SP wants to be taken seriously out-of-the-box (instead of having a bunch of devs write up a bunch of custom code).

re: Date Functions in Calculated Fields

QG — Tue, 15 Sep 2009 00:17:48 GMT

This worked great Amber thanks! I don't know what all these other guys are talking about. if they follow the instructions it works...

re: Date Functions in Calculated Fields

Silvia — Tue, 27 Oct 2009 11:36:13 GMT

The solution works perfect for me, I had Have to change the separator comma by the semicolom because in my sharepoint have installed Spanish languaje pack and it uses the comma as decimal comma.

The formula that works in my site is: =IF(MONTH([Today])=MONTH([Request Date]);"Yes";"No")

Thank you for your post.

re: Date Functions in Calculated Fields

David — Thu, 29 Oct 2009 20:36:58 GMT

If you have Problems to Query the Calculated Field read my Blog:

http://www.schonebeck.net/?p=5

re: Date Functions in Calculated Fields

Brent — Mon, 02 Nov 2009 15:05:43 GMT

This technique DOES work however has the limitation that the "Yes/No" value calculated only at item creation time or when the "today" column is added and subsequently deleted. You will find that as the current crosses into a new month, the "OpenedThisMonth" column will not be automatically recalculated.

To make this a viable solution, someone with list admin privileges would need to recreate the fake today column and delete it again manually each month.

re: Date Functions in Calculated Fields

Ryan — Fri, 27 Nov 2009 12:16:41 GMT

I don't get why people keep saying "the Today trick works - it just doesn't update"? Doesn't that make it about as useful as a parachute that opens... 5 foot above the ground? ;)

I've talked the today trick here -

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

@Brent - the abve also includes some links to workarounds by Dessie such as a batch job that can automatically add/remove the column to keep it up to date.

I've also blogged about an alternative method for filtering items from the current/previous calendar month

http://blog.pentalogic.net/2009/11/howto-filter-items-current-calendar-month-view-sharepoint/