0

I have a simple SharePoint item list . Consider this specific spreadsheet for an example, but I need an general solution to work as a Excel formula.

A1: contains a date. B1:Status contains a status Available/unavailable/. C1:LastAvailable date has nothing and should get result. When a change is made to B1, I want to get notified about it and the resulting formula's update in C1 with the date of update.

I tried multiple methods to work, but this is working in Excel with the helop of MAcro and functions such as TODAY() and NOW(). I am unable to accomplish this on Sharepoint.

IF(B:B="Available",TEXT(NOW(),"dd/mm/yyyy  hh:mm"),IF(ISBLANK(Status),"","NULL"))

Above is the formula which I used to get the state change from unavailable to Available . Similarly below one for vice versa

=IF(B:B="Assigned",TEXT(NOW(),"dd/mm/yyyy  hh:mm"),IF(ISBLANK(Status),"","NULL"))

Note: I dont have access to Sharepoint server, Unable to use Sharepoint designer.

Improve this question
2
  • 1
    For notification, you can use sharepoint default alert notifications. For date, I would have recommended you to use workflow. But you don't have an access to designer.
    – Ganesh Sanap - MVP
    Commented Apr 22, 2019 at 16:58
  • Hello @GaneshSanap, Thanks for the suggestion. In case if I have access to designer what are the steps to create workflow. I am new to SharePoint and I am using SharePoint 2007
    – Sankareswar
    Commented Apr 22, 2019 at 17:12

2 Answers 2

Reset to default
0

as I am reading the requirement, you want to hold the 'Last Available Date' when the Status changes from 'Available' to 'Unavailable'. For such requirement, calculated columns are not best fit. I would recommend you to use, either Workflow/MSFlow or use Event Receiver/Remote Event Receiver/Webhook (based on your environment). The reason is, you cannot use circular references in SP Calculated columns to retain 'Last Available Date' values, also from SP Formula syntax IF(condition, TRUE, FALSE) you cannot eliminate else condition like in traditional programming.

Using the below formula, you can store the current time stamp when the Status column change to available, and put the minimum date when it changes back to 'Unavailable' like '01/01/01'.

=IF([Status] = "Available", NOW(), "01/01/01")

enter image description here

Also, as @Ganesh mentioned, you can subscribe to list alerts to notifications when a new Item is created, modified or deleted. If you want to notify only when the Status column changes to Available, then you need to have workflow on item created and modified action which send email alert.

Improve this answer
2
  • Thanks for the response @ThinkB4Code. I tried using Workflow as well and I am out of logic.
    – Sankareswar
    Commented Apr 24, 2019 at 10:30
  • Are you using SPD WF or MS Flow?
    – ThinkB4Code
    Commented Apr 24, 2019 at 21:51
-1

This is how I would craft the equation in a Calculated Column in your list:
=IF([Status] = "Available",TEXT([Modified],"dd/mm/yyyy hh:mm"),IF(ISBLANK([Status]),"","NULL"))

  1. Reference another SP Column by using its display name in brackets, e.g.: [Status] and [Modified]
  2. You cannot use the volatile TODAY or NOW variables in SharePoint calculated columns if the formula has spaces in it.

References:

Improve this answer
1
  • I disagree to your point number 2, as you can refer to Today() and Now() in calculated columns.
    – ThinkB4Code
    Commented Apr 22, 2019 at 22:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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