Forum Discussion
Help Needed: Calculating Difference Between Two Date Columns in MS Lists
Hi everyone,
I'm having trouble calculating the difference between two date columns in Microsoft Lists. Every time I try to use a formula, I get the following error:
Sorry, something went wrong. The formula contains a syntax error or is not supported.
Here are some of the (AI-generated) formulas I've tried:
- Basic Difference Calculation:
- =IF(ISBLANK([End Date]) OR ISBLANK([Start Date]), "", [End Date] - [Start Date])
- =IF([End Date] >= [Start Date], [End Date] - [Start Date], "")
- Using DATEDIF Function:=IF(ISBLANK([End Date]) OR ISBLANK([Start Date]), "", IF(DATEDIF([Start Date], [End Date], "d") < 0, "", DATEDIF([Start Date], [End Date], "d")))
- Checking for Valid Numbers:=IF(AND(ISNUMBER([End Date]), ISNUMBER([Start Date])), IF([End Date] - [Start Date] < 0, "", [End Date] - [Start Date]), "")
- Simple Blank Check:=IF(ISBLANK([End Date]), "Empty", "Filled")
Despite trying these formulas, I keep encountering the same error. Both [End Date] and [Start Date] are date fields.
Has anyone else experienced this issue or have any suggestions on how to resolve it? Any help would be greatly appreciated!
Thanks in advance!
Hi vchrnk
a simple calculation with the output as days between draft and publish date:
=[Publish by]-[Draft due by]
or a more complex to calc the hours between start and end date/time:
=CONCATENATE(IF(INT(([End Date and Time]-[Start date and time])*24)>0,CONCATENATE(INT(([End Date and Time]-[Start date and time])*24)," ",IF(INT(([End Date and Time]-[Start date and time])*24)>1,"hours","hour")),""),IF(MINUTE([End Date and Time]-[Start date and time])>0,CONCATENATE(" ",MINUTE([End Date and Time]-[Start date and time])," ",IF(MINUTE([End Date and Time]-[Start date and time])>1,"minutes","minute")),""))
You can find more informations in this Microsoft Support article: https://support.microsoft.com/office/examples-of-common-formulas-in-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3?WT.mc_id=DX-MVP-5004845
Best, Dave
Hi Dave,
Thanks for your reply. Indeed, the simple calculation works. However, in case the [End date] is still empty, it shows a negative value, which I'd like to avoid.
Hi vchrnk
you can try this calculation:
=IF(OR(ISBLANK([Publish by]), ISBLANK([Draft due by])), "", [Publish by] - [Draft due by])
Best, Dave
