Sharepoint 2013: DateTime Column Default Value Set to Next Half Hour (30 Minutes)

Introduction

Sharepoint 2013 supports List and Libraries which consist of columns. There are many types of columns supported by Sharepoint, e.g., Number, Text, Note, Choice, DateTime.

Also, calculated column supported by Sharepoint and based on specific formula value can be customised. This article focuses on DateTime column customization.

Background

I came across a requirement in which DateTime type of column should show next half hour (30 minute) date time. Example: current date is 12 Jan 2018 and time is 10:37 AM, then value should display as 1/12/2018 11:00 AM.

1/12/2018 10:37 AM ==>  1/12/2018 11:00 AM

12/30/2015 02:07 PM  ==>  12/30/2015 02:30 PM

Using the Code

A DateTime type of column supports calculated default value. So formulas can added to calculate required values. In this case out of box functions are used with some time calculation.

Steps to follow:

1. To apply formulas to DateTime column, go to list and its list settings.
2. Go to DateTime column (It will show configuration)
3. In "Date and Time Format" section, select "Date & Time"
4. In "Default value" section, click on calculated value (in below textbox formula can be inserted)

There are many functions as below which provide different values for date time.

• =Now() => Gives current date time
• =YEAR() => Gives year of provided date
• =MINUTE() => Gives minutes of provided date Eg. 1/12/2018 10:37 AM => 37
• =IF() => Check condition, example =IF([Column1]=15, "OK", "Not OK")
• =ROUNDDOWN([Column1],2) => Rounds 12.5493 down to the nearest hundredth, two decimal places (12.54)

Logic to calculate next half hour slot is Get current date time and remove minutes from it and add 30 min. So next half hour value can be fetched.

/// Formula
=NOW()-1  
// Above Will Give Yesterday Date. =NOW()-4 This Will Deduct 4 Days

=NOW()-(1/24)
// This Will Give Date Time Before 1hr. =NOW()-(1/24*4) This Will Deduct 4 Hours From Current Time

=Now()-(1/24/60)
// This Will Give Date Time Before 1minute. 
// =NOW()-(1/24/60*4) This Will Deduct 4 Minute From Current Time

=NOW()-((1/24/60)*minute(now()))+((1/24/60)*30)
// This Will Give Date Time for Next Half Hour Slot. 
// =NOW()-((1/24/60)*minute(now())) This Will Deduct Current Minutes From Current Time.
// Eg.  1/12/2018 10:13 Am  =>  1/12/2018 10:00
// but Still There Is Error if Date Is 1/12/2018 10:33 Am => 1/12/2018 10:30 Am

=If(minute(now())<=30,(now()-((1/24/60)*minute(now()))+((1/24/60)*30)),
(now()-((1/24/60)*minute(now()))+((1/24/60)*60)))
// Final Solution Is Above Formula. This Will Give You Correct Next Half Hour Slot.

As per requirment developer can add or deduct Days/Seconds/Hours even can format date. Dates diffrence and Age can be calculated. Refer below links for more information.  

Language Scenario :

Above article elaborate use of functions in English language only. Its is possible that other sharepoint site may have multi language support. Example site supporting English and German language, developer need to update functions as per language.

Function conversion is needed as per required language.

Conversion example from English  ==>  German.

= IF (OR ([Column 1]> [Column 2]; [Column 1] <[Column 3]); "OK"; "Not OK")      

So conversion is  

=WENN(ODER([Spalte 1]>[Spalte 2];[Spalte 1]<[Spalte 3]);"OK";"Nicht OK")

Below article is usefull for getting correct conversion.

* German language function references.

Points of Interest

Various functions can be used according to requirement and usage can be referred from below links:

1. https://msdn.microsoft.com/en-us/library/office/bb862071(v=office.14).aspx
2. 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/

Happy Coding

Please comment if this is helpful or needs any update.

Happy to help! :)