SOLVED

Adding 1 everyday to a sharepoint list column

Copper Contributor

I'm trying to add 1 to a column everyday in sharepoint lists. We have a column called "Hire Instated" and another column called "Days in Hire". 

 

Essentially we need the days in hire to increase everyday. Alternatively, we need the Days in Hire column to be the amount of days since the date on Hire Instated.

 

Any advise or solutions would be appreciated! 

3 Replies
best response confirmed by NathanAlex (Copper Contributor)
Solution

Hi @NathanAlex,

do you really want to have a column that contains the number of days since the hire date or do you just want to display the number of days?

You can do the latter quite easily, if you apply the following JSON formatting on your "Hire Instated" column:

 

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

 

This would look like this

SvenSieverding_0-1691146685273.png


Just select the little arrow down next to your column name and select "Column Settings"->"Format this column". Then switch to "Advanced mode" and paste that JSON into the editor.

Best Regards,
Sven

@SvenSieverding 

Pretty cool solution! :)
Just one remark.

"txtContent": "=@currentField.displayValue"

doesn't work for me

"txtContent": "@currentField.displayValue"

is working

Hi there, I appreciate your help on this and I can confirm it works. Previously to this solution I had two further columns - Daily Rate and Total Hire Cost, and the formula for these no longer work with the new solution. Are we able to make it so we can calculate the days in hire X daily rate to produce total hire cost, while keeping your solution in play as well?

I'd appreciate your help :)

1 best response

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

Hi @NathanAlex,

do you really want to have a column that contains the number of days since the hire date or do you just want to display the number of days?

You can do the latter quite easily, if you apply the following JSON formatting on your "Hire Instated" column:

 

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

 

This would look like this

SvenSieverding_0-1691146685273.png


Just select the little arrow down next to your column name and select "Column Settings"->"Format this column". Then switch to "Advanced mode" and paste that JSON into the editor.

Best Regards,
Sven

View solution in original post