Share via

Calculated Column Date/Time Result Showing as 1280

Kelly Taggart 216 Reputation points
2020-08-11T00:03:01.717+00:00

I have a Checklist that has some calculated columns to work out when the next Task Date will be. For my Monthly and Quarterly tasks, if the task falls in the next calendar year, it is giving "1280" or "5" as the result (see below).

16859-calculated-column-2.png

The "Start Date" column is just a Date/Time column. Below is the formula for the Calc_TasxNextStartDate column.

=IF([Task Recurrance]="Monthly",IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=1,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=2,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=3,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=4,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=5,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=6,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]+2),HOUR([Start Date]),MINUTE([Start Date]),),IF(WEEKDAY(DATE(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date])),2)=7,DATETIME(YEAR([Start Date]),MONTH([Start Date])+1,DAY([Start Date]+1),HOUR([Start Date]),MINUTE([Start Date]),),0))))))))

I'm assuming I'm missing something small but can't pick up what it is - any suggestions?

SharePoint Server
SharePoint Server
A family of Microsoft on-premises document management and storage systems.
2,260 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. JoyZ 18,046 Reputation points
    2020-08-11T07:23:33+00:00

    I could reproduce your issue per my test, I suggest you use Date function instead of DateTime in this case.

    For example, if we want to add 36 minutes to the start date, we can use the start date+1/24/60 * 36 to calculate the value, however if you want to accurate the month to the minute, we need to calculate how many days each month actually has, this is hard to say.

    More information for your reference:

    https://www.premierpointsolutions.com/training/help-and-how-to-articles/how-to-add-and-subtract-hours-and-minutes-from-date-and-time-fields-in-sharepoint-lists/

    https://support.microsoft.com/en-us/office/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b4e-45ce-b170-bf7ebf6988b3