CODEWITHSUNDEEP
sharepointdateformula
user2312463
user2312463

Reputation: 1

How do I make a sharepoint formula that calculates date different based on when a value was last entered

Here's the problem I'm trying to solve. I have an Infopath form that users fill out and submit to a SharePoint library, and every field on the form is mapped to a SharePoint column.

Column A will have the name of a department, and column B will have the date that an occurrence was submitted on that department. There are several departments to choose from. I need to make a calculated column (column C) that will show how many days its been since an occurrence was reported on a specific unit.

Data will be set up like this:

A      B            C
1 South 4/20/2013   0
2 East  4/15/2013   8
3 South 4/18/2013   5
1 South 4/23/2013   0

C should calculate the amount of days since the last occurrence was reported for a specific unit. Assuming the current date is 4/23/13, column C should so the following values for each unit. 1 South would be 0 since there was an occurrence reported on the current day.

Upvotes: 0

Views: 40522

Answers (3)

user7438120
user7438120

Reputation: 1

In order to use the "today" function; which SharePoint won't allow you to use, you must create a column for today's date. This is done by simply adding a date column--which you can keep hidden. Once you've created a date column you can use the formula =DATEDIF([Start Date],[However you labeled your date column],"d")

Upvotes: 0

JakobN
JakobN

Reputation: 46

For anyone looking for the answer to this: Using the =DATEDIF([B], [Today], "d") formula above in a calculated column will give you the difference in days between the two dates. If you need to further filter only the row with the latest occurrence, you need to configure a view. Grouping by [A], sorting by [C], then use SP Designer to limit the group count to 1, or use xslt to only show the first of each group.

Upvotes: 0

lem.mallari
lem.mallari

Reputation: 1275

You can create a calculated column and use [Today] and subtract it from the value of column B. So you can implement like below:

=DATEDIF([B], [Today],"d")

This will return the number of days between a column named "B" and today's date.

If you're interested on creating other formula for calculated fields you can check them out in more detail here.

Upvotes: 1

Related Questions