SharePoint Calculated Column formulas based on Date and Time

In SharePoint Calculated Column, A formula starts with an equal sign (=) like we do calculation in math. In create column page you will see one dedicated space for writing formula, where you will write formula for calculated column.

Though you can use other formula & function in calculated column but we usually face difficulty with Dates so i am covering here some important dates formula for calculating column.

• Adding/Subtracting to a date

  • Add number of days to a date- Adding 30 days in my Project Start Date column

=[Project Start Date] +30

• • Add number of months to a date– Adding 3 months in my Project Start Date column

=DATE(YEAR([Project Start Date]),MONTH([Project Start Date])+3,DAY([Project Start Date]))

• • Add number of Years to a date – Adding 5 years in my Project Start Date column

=DATE(YEAR([Project Start Date])+5,MONTH([Project Start Date]),DAY([Project Start Date]))

• • Add combination of days, moths & years to a date- Adding 5 years, 3 Months & 30 days in my Project Start Date column

=DATE(YEAR([Project Start Date])+5,MONTH([Project Start Date])+3,DAY([Project Start Date])+30)

Note: You can also reference a number column for adding days, moths & years in your date column. Subtracting to a date is important when we set up reminder in a list via Flow.

• Getting Year/Month/Date from a date

  • Getting Year- returns year in a single line of text.

=TEXT([Project Start Date],”yyyy”)

• • Getting Year- returns year as integer.

=YEAR([Project Start Date])

• • Getting Month- returns full month name.

=TEXT([Project Start Date],”MMMM”)

• • Getting Month- returns first three letter of month name.

=TEXT([Project Start Date],”MMM”)

• • Getting Month- returns month in 2 digit number.

=TEXT([Project Start Date],”MM”)

• • Getting Month- returns month in number.

=TEXT([Project Start Date],”M”)

• • Getting Day- returns full day name.

=TEXT([Project Start Date],”dddd”)

• • Getting Day- returns first three letter of a day.

=TEXT([Project Start Date],”ddd”)

• • Getting Day- returns days in 2 digit number.

=TEXT([Project Start Date],”dd”)

• • Getting Day- returns days in number.

=TEXT([Project Start Date],”d”)

• Calculating the difference between two dates : Use of DATEDIF function

  • Getting number of days between  the two dates.

=DATEDIF([Project Start Date],[Project End Date],”d”)

• • Getting number of days between  the two dates ignoring the year part.

=DATEDIF([Project Start Date],[Project End Date],”yd”)

• • Getting number of months between  the two dates.

=DATEDIF([Project Start Date],[Project End Date],”m”)

• • Getting number of months between  the two dates ignoring the year part.

=DATEDIF([Project Start Date],[Project End Date],”ym”)

• Checking Leap Year:

  • The data type returned from below formula should be: 
    

    Single line of text

=MONTH(DATE(YEAR(TODAY()),2,29))=2

• Calculating week number from Date: 

  • Please refer my this post-How to get week number in SharePoint?

There is one more interesting use of calculated column in getting Half day marked in SharePoint Leave application. For this you need to calculate Total number of days between leave start and end date and if there is half day provision in your application. Then you can use following formula to include half day in total leave days-

=IF([Half Day Leave]=TRUE, [Total Leave Days]+0.5, [Total Leave Days])

Thank you

-Gopenly