Can sharepoint subtract one from a column everyday?

Question

I have a SharePoint column named Days Change_Ship, that is calculated using other columns. I was wondering if SharePoint has some sort of capability of subtract one from this column every day. I could possibly do this through power automate but I was wondering if sharepoint itself can do it.

Calculated Column (Days Change_Ship):

Answer 1

Hi @Lopez, Ahiezer ,

My idea is to set the item to decrease by one every day starting from today. In advance, there is a point to note here. Since the Today() function in the calculated column formula does not automatically update daily based on the current date. It’s updated only when the item is updated, otherwise it keeps the original value. If you don’t update the item for a month, the today() function will keep the same date for a month. So I've toiled to find an alternate solution, but it might be a bit of a hassle. My workaround is to display values without calculating columns. You can use any column and format it as JSON to display calculation results. JSON allows you to use @now , a placeholder for today’s date. Unlike the today() function in calculated column formula, @now will give you always today’s date. That allows you to calculate the difference in days between today and the other date. Here are my specific steps:

1.Below is my test column:
calnub column--calculated column--like your Days Change_Ship column
today column--calculated column--set today as a time node
distance column--normal number column--show the final number after reduction
tomorrow column--date and time column--Later I will use it to show you the comparison results

2.Format distance column. Calculated according to today, do not subtract one.

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

3.Compared with tomorrow's date, the number will be reduced by one. Just as a comparison to let you see the numerical difference between the two more clearly, there is no need to add this column.

4.Explain the JSON code:

• Number(…) will convert a date into a number in milliseconds
• Number(@now )-Number([$today]) will take today’s date in milliseconds and subtract from the set time node date(in today column) in milliseconds
• (1000*60*60*24) will calculate duration of a day in milliseconds: 1000 milliseconds * 60 seconds * 60 minutes * 24 hours
• floor((Number(@now )-Number([$today]))/(1000*60*60*24)) will take the difference between dates, divide it by duration of a day, and round the final number down to a full number

Hope I explained clearly.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.