Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

This is my first day using SharePoint and I got lost in trying to figure out how or if it can be done, custom column calculations, and if code is allowed in SharePoint? I had someone else create my own site and I have been in the entire thing all day and its exciting!

I figured I would be doing a lot on my own so I created a custom list. I am wanting to do the following:

  • Column A: Add the date you wish
  • Column B: Would take the actual date and calculate how many days from the added date in Column A
  • Column C: Will enter the closed date that will stop the counter in Column B

In general I am creating a ticket system in SharePoint, but I don't know if it can be done or where to look for it.

Edit: Example of what I am looking for

Date:     Days    Closed
2/19/2013 0 Days 

If a closed date is entered tomorrow

Date:     Days    Closed
2/19/2013 1 Days  2/20/2013

Incremental for days will end. If a date is not entered in the close column the days will keep adding.

share|improve this question
    
is Column B entered by the person entering the item? e.g. Column A + Column B, and Column C needs to be automatically generated? –  Quinn Johns Feb 19 '13 at 23:25
    
no I was hoping ColumnB would be automatically updated but Im still trying to find some documentation and books are sketchy on reviews for SharePoint 2013. –  Gramps Feb 19 '13 at 23:36
add comment

2 Answers

up vote 1 down vote accepted

The most obvious choice, calculated column, generally will not do for your counter in Column B, because calculated columns are re-calculated only when either the item is updated or the column itself is updated.

The other option would be to display the calculation dynamically on the page load, this can be done e.g. using XSLT or JS which is an intended way of modifying list views in SharePoint, yet this calculation will be applied only when a user visits the list view page. Thus it will display correct values, yes, but for a ticket system it hardly will be of any use, because obviously you should trigger some action when the "Closed date" is reached?

So I would consider two options here:

  1. Calculated Column + Timer Job: in this case you should create calculated column (please refer to this article on MSDN, "Date and time" formulas section) and also you should develop a SharePoint timer job which will update your calculated column each day so the values will be re-calculated correctly.
  2. View Customization + Workflow: in this case you just customize the list view so it shows additional column with dynamically calculated information, and also you will need to create a workflow which will wait until your "Closed Date" (Column C) and do something when it's there. Example of such workflow can be found in this article: Item expiration reminders in SharePoint using workflows. List view customizations could be a bit hard to delve into from your first days in SharePoint, but I think it's a good investment of time if you will work with SharePoint often. Generally such customizations are done either with JS (SharePoint 2013) or XSLT (SharePoint 2010 and before).

So as you see, your question is not that simple as you might have expected, and there is no two-line solution out there. I estimate it would take 1-2 hours for me to implement either approach, and you might need 1-2 days for that.

share|improve this answer
add comment

What you are looking for is the Calculated Column. Basically, you have Columns A and C which is a Date (make sure C does not have a Default value). There is a trick before - SharePoint does not allow direct use of otherwise valuable tokens, such as [TODAY] (which returns current date/time). In order to benefit from these dynamic things, use the trick here http://abstractspaces.wordpress.com/2008/05/19/use-today-and-me-in-calculated-column/

In summary, you create a new DATE column called Today, which has Default value of [TODAY].

That you shall use further in creating column B using the CALCULATED Column type and select Return type as Number for Column B because you are looking for a return in DAYS and use

=IF(LEN(C)>0,DATEDIF(Today,A,"d"),DATEDIF(C,A,"d"))

"if we have a value in the C column the value in B the difference in Days = C- A, otherwise the difference between Today-A"

share|improve this answer
    
I edited the post to better explain what I am looking for. –  Gramps Feb 19 '13 at 22:08
    
try it and let me know –  C. Marius - MVP Feb 19 '13 at 22:21
    
I will thanks. I never saw what official language is preferred for SharePoint? –  Gramps Feb 19 '13 at 22:30
    
What do you mean by "language"? Calculated formulas is more a declarative way to enhance features, similar to Excel. –  C. Marius - MVP Feb 19 '13 at 23:22
    
well in Excel you are allowed to code VB. Can SharePoint allow Java, VB, C# or the like? –  Gramps Feb 19 '13 at 23:24
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.