Adding Time to DateTime Fields in SharePoint Lists
Recently a fellow list member ran into a snag while trying to create a calculated field of the form [DateTimeField1 + TimeValue]. Ostensibly, his problem was the lack of seconds in the displayed value; in reality, however, the initial approach betrayed an all-too-common misunderstanding of the way date and time values are treated in SharePoint.
The heart of the problem turned out to be the TIME function. This takes a set of three integer parameters (hours, minutes, seconds) and converts them into a decimal value as a fraction of a day. For example, the conversion of 10:20:15, or TIME(10, 20, 15) would yield a return value of 0.43. This can't actually be added to a datetime field unless you convert the field value with the same function, which leaves you with just the sum of the times (and not in a very usable format).
The alternative, then, is the somewhat more intuitive (if more obscure) method of simply adding the two datetime/time fields, like this:
=[DateTimeField1] + TimeValue
For the final piece, the field needs to be displayed in the desired format, which in this case includes the display of seconds. The TEXT function -- which converts a given value type to a string based on the provided formatting expression -- will do the job.
=TEXT(([DateTimeField1] + TimeValue), "m/d/yyyy h:MM:ss")
This formatting should be familiar to anyone who has worked with date and time data in Microsoft development tools, whether VBA in Office or C# in ASP.NET.
Obviously the string can be altered to include only date, only time, or a longer date format; this should serve as a starting point for whatever time calculations you might need.
