1

I have a list of participants in a conference. All participants are arriving and departing on numerous different flights. I am trying to create a Power Automate flow that will send a Teams message as a warning when their flight is one hour or 30 minutes from arrival. To do so, I have two fields:

  1. A timestamp field (titled “Flight Arrival”) in the SharePoint list for the arrival date and time.
  2. A number field (titled “Time to Arrival”) that displays the time remaining until the flight arrives. I do not use a calculation field. I use a number field and then add JSON to the column formatting. See my JSON below.
{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@now)-Number([$Flight_Arrival]))/(1000*60*60*24))"
}

The calculation appears to be working in some way, but it displays the wrong number of milliseconds. It is showing just 19684 milliseconds, which is about 32 minutes. In addition, it does update when I change the “Flight Arrival” field to a different date/time. Is my calculation correct? Am I using the right code to reference the “Flight Arrival” field ($FlightArrival)

See screenshot for reference below:

enter image description here

4
  • You have to use the internal name of your columns in the JSON. You can get the exact internal names of your columns by following this article: How to find the Internal name of columns in SharePoint Online?. Note: JSON formatting only customizes the display in list view/column, it does not actually save/store the value in column. So, you cannot use the JSON formatted value in Power Automate flow or Power Apps or list view filtering/sorting, etc. Nov 23, 2023 at 5:16
  • 1
    Thank you - that worked. I’ll update my code with the answer above. And thanks for pointing out that JSON formatting doesn’t store a value. I would appreciate it if you could point me in the right direction to implement what I want. Should I create a condition in Power Automate directly and then use an expression to calculate the time remaining between arrival and the current time?
    – matt
    Nov 23, 2023 at 6:07
  • Yes, you should do the calculation directly inside the Power automate flow. Nov 23, 2023 at 6:08
  • 1
    I am checking the status of this case. Please let us know if you would like further assistance. If you have any update about this issue, please feel free to post back. I am checking the status of this case. Please let us know if you would like further assistance. If you have any update about this issue, please feel free to post back.
    – Xyza_MSFT
    Nov 29, 2023 at 6:40

1 Answer 1

0

From the screenshot you provided, I can see that you are using "Date only" column named "Flight Arrival".

If you are trying to show the number of days until the Flight Arrival date, you can use the JSON like:

Example:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number([$FlightArrival])-Number(@now))/86400000) + ' days'"
}

Where FlightArrival is the internal name of your SharePoint list column. You can get the internal name of your SharePoint list columns by following this article: How to find the Internal name of columns in SharePoint Online?

Output:

enter image description here


If you are trying to show the difference in minutes, you can use JSON like:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number([$FlightArrival])-Number(@now))/(1000*60)) + ' minutes'"
}

Output:

enter image description here


Note: JSON formatting only customizes the display in list view/column, it does not actually save/store the value in column. So, you cannot use the JSON formatted value in Power Automate flow or Power Apps or list view filtering/sorting, etc.

If you want to check the days/minutes difference in Power Automate flow, you should do the calculations directly inside the Power automate flow using expressions.

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.