Resulting NaN when calculating between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-1260674%22%20slang%3D%22en-US%22%3EResulting%20NaN%20when%20calculating%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1260674%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Everyone%3C%2FP%3E%3CP%3EStay%20safe.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22JunHo_KIM_0-1585355600151.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F180094iB8A62A2A83C93C98%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22JunHo_KIM_0-1585355600151.png%22%20alt%3D%22JunHo_KIM_0-1585355600151.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20am%20using%20SharePoint%20Online%20lists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20The%20'DueDate'%20column%20is%20manually%20typed%20in%20date%26amp%3Btime%20format.%3C%2FP%3E%3CP%3E2.%20The%20columns%20'TODAY2'%20and%20'TODAY'%20are%20automatically%20generated%20by%20JSON%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20questions.%3C%2FP%3E%3CP%3E(1)%20I%20would%20like%20to%20calculate%20days%20left%20in%20column%20'DAY2'%20between%20'DueDate'%20%26amp%3B%20'TODAY2'%20using%20JSON.%20However%2C%20when%20I%20do%20that%20it%20returns%20'NaN'%20error.%20So%2C%20I%20tested%20using%20'Created'%20column%20instead%20of%20'DueDate'%20and%20it%20works!!!%20I%20thought%20it%20was%20because%20of%20format%20difference%2C%20so%20I%20manually%20typed%20in%20same%20format%20in%20column%20'Remark'%2C%20but%20the%20result%20was%20the%20same%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(2)%20I%20do%20not%20want%20to%20see%20decimal%20figures%20in%20'DAY2'.%20I%20tried%20to%20format%20from%20'Automatic'%20to%20'0'%20in%20column%20setting%2C%20but%20did%20not%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFYI%2C%20the%20JSON%20codes%20applied%20in%20'DAY2'%20are%20as%20below%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3CBR%20%2F%3E%22%24schema%22%3A%20%22%3CA%20href%3D%22https%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdeveloper.microsoft.com%2Fjson-schemas%2Fsp%2Fv2%2Fcolumn-formatting.schema.json%3C%2FA%3E%22%2C%3CBR%20%2F%3E%22elmType%22%3A%20%22div%22%2C%3CBR%20%2F%3E%22txtContent%22%3A%20%7B%3CBR%20%2F%3E%22operator%22%3A%20%22%2F%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22operator%22%3A%20%22-%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22operator%22%3A%20%22Number()%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%22%40now%22%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%2C%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%22operator%22%3A%20%22Number()%22%2C%3CBR%20%2F%3E%22operands%22%3A%20%5B%3CBR%20%2F%3E%22%5B%24Created%5D%22%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%2C%3CBR%20%2F%3E86400000%3CBR%20%2F%3E%5D%3CBR%20%2F%3E%7D%3CBR%20%2F%3E%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1260674%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELists%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264075%22%20slang%3D%22en-US%22%3ERe%3A%20Resulting%20NaN%20when%20calculating%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264075%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EI%20do%20see%20some%20empty%20values%20on%20the%20'DueDate'%20column%2C%20are%20you%20sure%20that%20the%20column%20will%20have%20no%20empty%20values%2C%20because%20if%20the%20created%20works%20then%20your%20column%20should%20unless%20there%20is%20an%20empty%20value.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1264087%22%20slang%3D%22en-US%22%3ERe%3A%20Resulting%20NaN%20when%20calculating%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1264087%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144422%22%20target%3D%22_blank%22%3E%40Sudharsan%20K%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EHope%20you%20are%20doing%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20mean%20space%20between%20the%20column%20name%20'DueDate'%20or%20dates%20in%20the%20column%3F%3C%2FP%3E%3CP%3E(1)%20If%20you%20are%20talking%20about%20'DueDate'%2C%20100%25%20sure%20there%20is%20no%20space.%3C%2FP%3E%3CP%3E(2)%20If%20you%20are%20talking%20about%20the%20dates%20in%20the%20column%2C%20the%20actual%20values%20are%20yyyy-MM-dd%20without%20space.%20The%20symbols%20are%20just%20JSON%20formatted%20ones.%20I%20was%20also%20wondering%20if%20it%20could%20fail%20my%20expectation.%20So%2C%20I%20tested%20using%20the%20column%20'%EA%B3%84%EC%95%BD%20%EC%8B%9C%EC%9E%91%EC%9D%BC'%20which%20did%20not%20have%20JSON.%20The%20result%20was%20the%20same.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20your%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, Everyone

Stay safe.

 

JunHo_KIM_0-1585355600151.png

I am using SharePoint Online lists.

 

1. The 'DueDate' column is manually typed in date&time format.

2. The columns 'TODAY2' and 'TODAY' are automatically generated by JSON code.

 

I have two questions.

(1) I would like to calculate days left in column 'DAY2' between 'DueDate' & 'TODAY2' using JSON. However, when I do that it returns 'NaN' error. So, I tested using 'Created' column instead of 'DueDate' and it works!!! I thought it was because of format difference, so I manually typed in same format in column 'Remark', but the result was the same error.

 

(2) I do not want to see decimal figures in 'DAY2'. I tried to format from 'Automatic' to '0' in column setting, but did not help.

 

FYI, the JSON codes applied in 'DAY2' are as below;

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "div",
"txtContent": {
"operator": "/",
"operands": [
{
"operator": "-",
"operands": [
{
"operator": "Number()",
"operands": [
"@now"
]
},
{
"operator": "Number()",
"operands": [
"[$Created]"
]
}
]
},
86400000
]
}
}

2 Replies
Highlighted
Hi,
I do see some empty values on the 'DueDate' column, are you sure that the column will have no empty values, because if the created works then your column should unless there is an empty value.
Highlighted

@Sudharsan K 

 

Hi,

Hope you are doing well.

 

Do you mean space between the column name 'DueDate' or dates in the column?

(1) If you are talking about 'DueDate', 100% sure there is no space.

(2) If you are talking about the dates in the column, the actual values are yyyy-MM-dd without space. The symbols are just JSON formatted ones. I was also wondering if it could fail my expectation. So, I tested using the column '계약 시작일' which did not have JSON. The result was the same.

 

Thanks for your help.