Date Calculations in SharePoint List with conditions

%3CLINGO-SUB%20id%3D%22lingo-sub-3213637%22%20slang%3D%22en-US%22%3EDate%20Calculations%20in%20SharePoint%20List%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3213637%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20do%20some%20complex%20calculations%20in%20SP%20and%20don't%20even%20know%20if%20SP%20has%20the%20capability.%3C%2FP%3E%3CP%3EIn%20a%20list%2C%20I%20have%20a%20Column%20%5BProposed%20Presentation%20Date%5D.%20There%20is%20also%20a%20%5BCreated%5D%20date%20column.%3C%2FP%3E%3CP%3EI%20want%2C%20in%20a%20different%20column%20%5BDays%20Before%20Late%5D%20to%20calculate%20the%20number%20of%20days%2C%20then%20give%20a%20message%20when%20threshold%20is%20met.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EProposed%20Presentation%20Date%20is%20July%201%2C%202022.%20Created%20Date%20is%20February%201%2C%202022.%20That%20would%20be%20150%20days%20exactly%20but%20I%20keep%20getting%20a%20value%20of%202%20when%20using%20the%20following%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DTEXT(%5BProposed%20Presentation%20Date%5D-%5BCreated%5D%2C%22D%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DTEXT(%5BProposed%20Presentation%20Date%5D-%5BCreated%5D%2C%22D%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20complex%20part%20is%20when%20this%20date%20is%20properly%20calculated%2C%20if%20it%20is%20over%20150%20days%2C%20then%20it%20should%20be%20flagged%20or%20prompt%20for%20a%20message%20stating%20it%20is%20late.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20assistance%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3213637%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3213720%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20Calculations%20in%20SharePoint%20List%20with%20conditions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3213720%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1317846%22%20target%3D%22_blank%22%3E%40mikehamm35%3C%2FA%3E%26nbsp%3BIn%20your%20calculated%20column%20you%20just%20need%20%3D%5BPresentation%5D-%5BCreated%5D%20with%20the%20result%20being%20a%20number%20with%200%20decimal%20places.%20Then%20you%20can%20format%20the%20result%20with%20JSON%20column%20formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EIntranet%2C%20SharePoint%20and%20Power%20Platform%20Manager%20(and%20classic%201967%20Morris%20Traveller%20driver)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello.

 

I am trying to do some complex calculations in SP and don't even know if SP has the capability.

In a list, I have a Column [Proposed Presentation Date]. There is also a [Created] date column.

I want, in a different column [Days Before Late] to calculate the number of days, then give a message when threshold is met.

 

Proposed Presentation Date is July 1, 2022. Created Date is February 1, 2022. That would be 150 days exactly but I keep getting a value of 2 when using the following formula:

 

=TEXT([Proposed Presentation Date]-[Created],"D")

 

=TEXT([Proposed Presentation Date]-[Created],"D")

 

 

 

 

 

 

The complex part is when this date is properly calculated, if it is over 150 days, then it should be flagged or prompt for a message stating it is late.

 

Any assistance would be appreciated.

4 Replies

@mikehamm35 In your calculated column you just need =[Presentation]-[Created] with the result being a number with 0 decimal places. Then you can format the result with JSON column formatting.

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

@mikehamm35 I forgot to add the screnshot. The calculated column is called Alpha and the formula was =[Expiry]-[Created] and it shows the days between the 2 correctly.

 

daysBetween.png

 

Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)

Thank you very much. I got the values I need. Now I am trying to create conditional formats which cause an alert to appear if the "Alpha Column" extends a certain amount. I am using InfoPath to create those rules because I don't think SP has that functionality.
But if it's a modern list InfoPath isn't supported.