Calculated Column [Today] work around?

Question

Ive been doing alot of reading into getting [Today] to work within a calculated column but im starting to wonder if theres a better solution staring me in the face

Basically we have an asset register where we highlight what asset is allocated to who. We'd like to highlight when someone has left the company and not returned the item. My initial thought was to create a leaving date column and set up a calculated column that says, when leaving date is less than todays date, flag the status as not returned (ive been asked to make it as visual as possible so was thinking of using calculated columns HTML capabilities to highlight the status as red)

Am i using calculated columns wrong here? Is there a way of using a workflow that will do what im looking for in a simpler way than the workarounds ive been reading online to the [Today] conundrum?

Answer 1

You could do this as a view. Just set the view to be filtered on Leaving Date < [Today], which will only show those who have left. I'm assuming you have a flag saying "Returned" or similar, which can be checked and then filtered out as well. It would be highly visible, as only the non-returned equipment/leavers will be displayed.

Answer 2

You cannot use Calculated columns to update on the viewing time. It only gets updated when you are adding or editing the item. So stop any further reading related to calculated columns.

Answer 3

Malin is partly correct.... you cant just use [today] as sharepoint cant update on the current date of today as it needs another column that is updated on a daily basis....

you can try:

1) create a column and call it 'today', set it to date only, set it to todays date and save that column.

2) create another column called 'leaving date', this is for the leaving date column... set it to date only and click ok.

3) create a calculated column and checkup on date time, only need to check on date.. and do:

[leaving date]<[Today]

next we need to set it to a bool, yes/no

4) goto list settings and remove today column...

before:

after

5) add a new list item into the list.. you should only have title and leaving date to enter, once submitted it then shows title (users name), leaving date(date person is leaving) and a yes / no (if its today and date has passed(yesterday or more) it will show yes else shows no).

result:

adding item:

and what i see:

first item user hasnt left, second item is the one i just made above and he has left!

, changing the images is another thing and can be done using JQuery,

http://www.sharepointusecases.com/2014/04/replacing-strings-icons-list-view-jquery-sharepoint-2010/

Answer 4

"You cannot use Calculated columns to update on the viewing time. It only gets updated when you are adding or editing the item. So stop any further reading related to calculated columns."

I disagree here.

try pasting the exact line

="<button onclick=""alert('Hello World!')"">Click Me</button>"

In one Calculated Column. SET THE DATATYPE TO NUMBER!!

This runs Javascript from a List View. Change the onclick handler to an onload handler on an IMAGE and Javascript will run immediatly after the image is loaded (ONLY WHEN DISPLAYING THE VIEW in the Browser!) So if you can run Javascript.. you can run JSOM code to update any field you want... from a View.

It is a real pain though to get it working... you can not debug javascript inside a Calculated Column... any typo you make displays a Calculated Column error.. no clue whatsoever what is wrong. I am working on some Apps to facilitate development.