SOLVED

Countdown to due date in SharePoint 365 list

Copper Contributor

Hello!

 

I'm using a SharePoint 365 list as an action tracker. My list contains the due date. I would like to add a column that contains a count of days remaining to the due date. 

 

I tried the datedif function together with 'TODAY', but it does not work. I guess I need to have an additional column with today's date to use this one? Was hoping to create it using workflows but these were retired in SharePoint 365. Is there a way to do it differently?

 

Would much appreciate your help.  

 

10 Replies

@kobi1991 

You could apply JSON Formatting:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))+' day(s) remaining'"
}

 

 


Best Regards,

Sven

@SvenSieverding 

 

Thank you, Sven! Apologies but I'm completely new at this - could you please explain where in the code I should put my 'due date' column? 

 

Karolina

best response confirmed by kobi1991 (Copper Contributor)
Solution

Hi @kobi1991 ,

 

sure. This is SharePoint Json list formatting code that formats a column.

https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

Got to you SharePoint View, select the header of your "Due Date" column and select "Column Settings->Format this Column"
duedate.png
Copy the Json Code here
formatcolumn.png

You don't have to change the formula, as "@currentField" references to the selected Column.

Best Regards,
Sven

Thank you so much, Sven! It works, but I'm wondering if there's a way to do it the way the 'Countdown' column corresponds to the 'Due date' column as I would like to have a view of the Due date itself, and when adding a new item the 'Countdown' column would populate itself.

Is it at all possible?

Thank you,
Karolina

Hi @kobi1991 

 

You could create a calculated column "Countdown" of type "Date" with the formula

=[Due Date]

and then apply the formatting there. 

Then you have two columns with the same value, but one is displaying the date and the other one is displaying the countdown.

Best Regards,

Sven

Hi @SvenSieverding,

 

I have a question about your json code, i have tried to apply the code into my list but all the dates that have past due displays for example "-29 days", is there any code that could make all the dates that have past due displays "0 days"

 

Thanks

Best Regards, 

Faric 

Hi @faric1311,

in that case you can just wrap it into an if clause.
This displays the remaining days, if the calculated value is larger than 0 and "no days remaining" if that value is below or equal 0.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>0,floor((Number(@currentField)-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))+' day(s) remaining','no days remaining')"
}

 
Best Regards,
Sven

Hi Sven!

I am having a similar issue getting 2 columns in Microsoft Lists - I was hoping to have column A list a date that I manually type in, and column B present a countdown until that date.

I was having issues modifying the information you provided to match my Lists, would you happen to have any insight?

Kindly,
Chase

Hi @Chawoo,

sure.

@currentField

refers to the column where the format is applied on. But you can refer other columns by their internal Name.
Given you have the column "Other Date Column" then you reference that as "[$OtherDateColumn]", so the formula becomes this

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "txtContent": "=if(floor((Number([$OtherDateColumn])-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))>0,floor((Number([$OtherDateColumn])-Number(Date((getMonth(@now)+1)+'.'+getDate(@now)+'.'+getYear(@now))))/(60*60*24*1000))+' day(s) remaining','no days remaining')"
}

 Now apply that to your other (Countdown) column.

SvenSieverding_0-1692164411409.png


Best Regards,
Sven

@SvenSieverding Is it possible to leverage this same approach but list days and hours, as opposed to just days? Appreciate your advice.

1 best response

Accepted Solutions
best response confirmed by kobi1991 (Copper Contributor)
Solution

Hi @kobi1991 ,

 

sure. This is SharePoint Json list formatting code that formats a column.

https://learn.microsoft.com/en-us/sharepoint/dev/declarative-customization/column-formatting

Got to you SharePoint View, select the header of your "Due Date" column and select "Column Settings->Format this Column"
duedate.png
Copy the Json Code here
formatcolumn.png

You don't have to change the formula, as "@currentField" references to the selected Column.

Best Regards,
Sven

View solution in original post