Pingback from  Links (6/26/2007) « Steve’s SharePoint Stuff

Mark:

I read the blurb in this entry about how "... 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..."  

Can you help w/ this code?  

re: jfriend80 post

I second his post.  I'm using the technique you outline to generate an 'aging' summary report and as a work around am having to add and delete the  [Today] column each day.

Would really appreciate some more insight on how to make this more dynamic and hopefully avoid this small, but manual, daily work around.

Brilliant!  I have been looking all morning for some examples of formula syntax for a calculated column in SharePoint 2007 and after much angst finally found this post.  Exactly what I needed.  Thank you sir.

Seth

Recently I was asked to create new Monthly and Quarterly DVWPs for the "Time Card Management"

Pingback from  Blogger Loser  » Blog Archive   » Example of MOSS calculation fields for Month-Year and Quarter-Year from date column

Pingback from  16 Links Today (2007-09-10)

Some more insight on using the Object Model to perform the auto [Today] creation and deletion would be helpful.  I, personally, would like to know just how that is done.  It would be extremely helpful....

Thanks for the post.

Do you have any idea how to display the Month Name like January, February etc. using Calculated Column in SharePoint 2007?

hi,

this was an excellent blog.

I've some task similar to this.

About my task:

Its about Issue Tracker, in which Due Date should be automatically generated based on the Priority(LOW-MEDIUM-HIGH) givent by the respective person.

I'm little bit confused in doing this task.

Please help me in doing this task.

And also I faced a problem regarding the maximum length of Characters the Calculated Value field takes.

I faced a restriction problem in the Calculated value field

Thankyou all in Advance.

The tip's worked for me but my web part won't let me filter against anything other than currentuser or currentdate - pointless considering I need to filter by week!

If anyone knows a way around that I'd be well impressed.

As for displaying the month name, try formatting your date field with a formula - eg

<xsl:value-of select="substring-before(ddwrt:FormatDate(string($fieldvalue) ,1033 ,3),',')" />

How can I access the xml and xsl file to make the formatDate changes. Also will the changes be applied to a single instance of a custom list or will it carry over to others?

I found a solution to grouping my records by Year and date format with the date written in text not numbers.

My problem was that the year was formatting as 2,007 and the month ex. 10 for October

Solution:

In SharePoint I created two calculated columns with date&time data type:

=TEXT(Created,"yyyy")

for the year. It displays 2007

= TEXT(Created, "mmmm")

for  the month. It displays October. "mmm" will produce Oct.

Next

I created two more calculated columns with single line text data type, for grouping. Any other type generated an error. The formulas I used for the columns:

=[Year]

=[Month]

That is, the names of the first two columns

Last

I gouped by year then by month  

You may have to enter the formula column name in brackets like this

=TEXT([Created],"yyyy")

Mark (or anyone else that can help),

Is there really no way to have a dynamic calculation for Due Date - [Today] ?  

I have many metrics I'd like to track that require this type of calculation and I'm shocked that SharePoint doesn't provide that functionality.  Is that really true?

There have been discussions about creating a reference column with a default date of [Today], then referencing that column in another one, and then deleting the original reference column.  As you know, that works for that one day, but not for following days.  

I feel like everyone here is hitting their head against a wall.  Any new ideas?

You could write a simple batch program to run every night at midnight (or whatever's best for your traffic load) that traverses a list of items and adds 1 to the age of each item.  Doing it in SharePoint with a calculated column doesn't sound like it's meeting anyone's needs.

Does anyone know of a way to display an image  (stored in a picture library) based on the value of a field? I'm trying to put a simple project dashboard together, and have a field that contains a status of "Red", "Amber", or "Green" - instead of displaying the word, I would like to display an appropriate icon/image?

is there a place to see all of the available functions available?  i.e. =TEXT ... or the others... Most of the ones i have needed thus far are there, however Others like rand ... i have not been able to find

I should add a little more as to what i'm trying to do.  I am trying to create an unique trouble ticket id column.. . i.e. your trouble ticket number is 154XBC ... or something to that nature.  But I don't want to use the AUTO ID field as I am trying to create a system that can be used across multiple sharepoint sites universally.  

This is working for me: (admittedly I'm in my second day on Sharepoint)

Add a column (Age) to your list - use a text type and leave it empty

Create a DataView WebPart in Sharepoint Designer.

Add the columns you require to view including the Age column.

select a cell in the Age column and then click on the Data View menu - click on Insert formula.

then insert a variant of :

concat(substring-before(ddwrt:TodayIso(),'-'),substring(ddwrt:TodayIso(),6,2),substring(ddwrt:TodayIso(),9,2)) - concat(substring-before(@Duedate,'-'),substring(@DueDate,6,2),substring(@Duedate,9,2))

In my application this will show if a task is overdue if the result is positive. I'm also using the formula for conditional formatting of the dataview/list . I haven't checked yet if you can sort/filter using this formula.

Any gurus out there know?

Hi

I was wondering if it is possible to work out the dates based on something other than date created.

For example, I have an expiry date - Date Expires.

How could I build this in to the formula so that it works out the days until expiry ?

Iain

Thanks for the tips. I was looking into other formating methods and found an even easier method for getting the differences (still need to add/remove the Today column): the DateDif function

See

office.microsoft.com/.../HA011609471033.aspx

(also "my URL" for easier clicking)

As I understood to make Today in this formula up to date there should be a kind of TimerJob implemented to frequently update items on a list?

Pingback from  www.sharepointblogs.com/.../howto-using-sharepoint-calculated-columns-to-display-a-list-item-as-quot-x-quot-days-old.aspx

I need a way to have the value change when Today changes.  I wish there were a way to override the Filter as there is in Content Query Web Parts.

I have a list. I need to create a field called ID1 that contains tha value of the ID + 8. The change column doesn't let me do that. How do I overcome this problem

This post really help me a lot, but is there possible for us to calculate the time, i mean in hour, not only in year, month and date. thanks

=CONCATENATE(ABS(HOUR(today())-HOUR(today()));":";ABS(MINUTE(Modified)-MINUTE(Created)))

this works