1

I'm pretty new to JSON.

I have a SharePoint list with employee schedules (shifts) as seen below: enter image description here

My OnClock column uses the following to determine if (now) time is within the stipulated shift start and end times. This works great.

JSON

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if((Number(@now)-21600000)/1000%86400>Number(Date([$Time])-28800000)/1000%86400,if((Number(@now)-21600000)/1000%86400<Number(Date([$EndTimeNeeded])-28800000)/1000%86400,'Yes','No'),'no')"
}

However I'm having trouble since Flow does not "pick up" the value. As a matter of fact if I open an item up --> OnClock is otherwise empty ( "-" ).

enter image description here

Is it possible to have the value that's visually displayed actually write to the true value of the item so Flow can read it?

Thanks in advance.

1

It's not possible to have the value displayed in JSON formatting actually write to the true value of the item.

As a workaround, I would suggest you use calculated column instead.

1
  • Thanks for confirming, can you show me an example of how to create a calculated column to replicate what I'm trying to accomplish to have Flow read it's value? – Jeff Speer Dec 11 '20 at 15:05
0

This is not possible using JSON formatting. Using JSON formatting, you can only show/display the value and you cannot save it to the list.

Possible workarounds:

You can manage the calculation using either of below:

  1. By creating calculated column and using its value in flow.
  2. Directly calculate the value based on dates in flow. You can use it in flow as well as you can update the value back to SharePoint.

Update from comments:

Check below links as references for calculated columns in SharePoint:

  1. Date and time formulas
  2. Examples of common formulas in lists
5
  • Thank you for confirming. Can you show me an example of how I can go about creating a calculated column to replicate what I'm trying to do? I'm under the assumption it would be in fact easier to have a calculated column and have Flow use it's value than doing the math in Flow. Thanks again. – Jeff Speer Dec 11 '20 at 15:01
  • Check my updated answer for reference links. Your calculated column formula will be something like: =IF(AND(TODAY() > [Start Date], TODAY() < [End Date]), "Yes", "No"). Replace column names with your actual column names (use display names, the one which you see in list/table view). – Ganesh Sanap Dec 11 '20 at 17:17
  • (TODAY) takes date into consideration also right? How can I only have it take the Time into consideration? My [Start Date] and [End Date] have dates in them but they can be ignored. Just wanted to consider time stamps. Ex: (7:00 AM), (4:00 PM). – Jeff Speer Dec 11 '20 at 17:35
  • Check "Date and time formulas" link in my answer. you can calculate the difference between two times as well. – Ganesh Sanap Dec 11 '20 at 17:39
  • Right, but I want to ignore the date completely. My column needs to be of type Date and Time but the Date value needs to be otherwise irrelevant. How can my calculated column ignore the fact that there's a Date in [Start Date] and [End Date]? I read the documentation and aren't able to wrap my head around this. – Jeff Speer Dec 11 '20 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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