Sharepoint Forum

Ask Question   UnAnswered
Home » Forum » Sharepoint       RSS Feeds

Using Time in SharePoint

  Asked By: Urjita    Date: May 02    Category: Sharepoint    Views: 7522

I have a case where I need to take a Time like: 10:10:10 (ten hours, ten minues,
and 10 seconds, and subract (or add) a number like 1:10 (one minute and 20
seconds. The result should be 10:10:09, (ten hours, nine minutes.

So far, I can't get seconds to appear using the calculated column, even though
the formulas do support seconds.



9 Answers Found

Answer #1    Answered By: Delilah Mcpherson     Answered On: May 02

What is your formula, and how does it display?

Is there a chance that the seconds are somehow rendered but not visible in
the browser?

Answer #2    Answered By: Gobinda Navalagi     Answered On: May 02

I use the TIME formula, like below.

=[Date & Time]+TIME(0,0,[Offset Number])

[Date & Time] in this example would be 3/5/2009 1:00 PM - but notice I can't
display seconds in a list.

The [offset number] in this example would be 30 (meaning 30 secoonds)- This
number is set as a NUMBER. SharePoint has no way of setting seconds that I know
of, and that may be my dilema.

Yet, if you look at the formulas  in the Help file, it does show a way to do
hours, minutes and seconds.

Answer #3    Answered By: Rose Silva     Answered On: May 02

Does the calculated  field save if you use this formula? According to the
reference at
office.microsoft.com/.../HA011610841033.aspx ,
TIME(0, 0, 30) would yield a decimal value of something like 0.000347. To
add this to a DateTime field, you'd probably have to convert the date/time
stamp using TIMEVALUE to get a similar decimal value, e.g.

=TIMEVALUE([Date & Time]) + TIME(0,0,30)

Another option would be to use VALUE([Date & Time]) + VALUE("0:0:30") and
see if that would work.

Of course to re-convert that into a meaningful time  figure, you'll have
to use the TEXT function. I'm not sure of its ability to display seconds,
but you should have that available (note that I'm extrapolating the time
syntax below based on my knowledge of past and current Microsoft date

=TEXT(TIMEVALUE([Date & Time]) + TIME(0,0,30), "m/d/yyyy hh:MM:ss")

Something like this *should* work, if anything does. Let me know how it

Answer #4    Answered By: Maegan Ellison     Answered On: May 02

You gave me some additional information I did not know, but here is what I came
up with to make this time  thing work:

=TEXT([Air Date & Time]+[Show Length],"h:mm:ss")

The [Air Date & Time] column  could be set to either text or a date & time
format. [Show Lenght] must be in this format; hh,mm,sec. For example 01:10:30.
The calculated  value with then be the air date and time + show length. Works

Your added tip .....""m/d/yyyy hh:MM:ss") gives me the way to combine the date
and time.

Answer #5    Answered By: Yahaira Shannon     Answered On: May 02

I've been trying to follow this thread, as I'm sure to run across a similar
situation, but I'm not up on formulas  and such. Could someone break this down a
bit and explain the end result  a little clearer?

Answer #6    Answered By: Doris Leach     Answered On: May 02

I'd be happy to give it a shot

The two date/time fields are being added together, and their result  is
being converted to a string value using the "h:mm:ss" format string as a
guide. The TEXT function takes a field of varied type and "strings" it
according to the format specified in the second parameter.

Answer #7    Answered By: Sumitra 2004     Answered On: May 02

Given that I got it to work using h,m,secods, I wanted to add  the date to that
calculation. Perter gave me the hint and I put it in the formula, and now I can
enter a date and time  in a column, then enter an offset time that includes
seconds, and display the results in another column  that has the calculation in
it. Here is the final formula:

=TEXT([Air Date & Time]+[Show Length],"m/d/yyyy h:mm:ss")

Explaination: [Air Date & Time] (In this column I enter as an example 03/05/09
10:45 AM) In the [Show Length] column I put 02:25:47.
The Calculated column containing the formula shown above will now contain:
3/5/2009 13:10:47

If you add more show length time the date will eventually wrap over to the next
day. You can also subtract in this formula by substituting the + sighn for a -

Answer #8    Answered By: Bhairavi Damle     Answered On: May 02

Ok... so is someone going to write all this date/time stuff up into a neat blog
post that spells it al out and the rest of us can then point to and reference
for easy use down the road?

Daniel A. Galant | MINDSHARP | 636-233-0762
MCITP - Enterprise Administrator, MCTS - SharePoint, MCTS - WSS v3, MCTS - OCS,
MCSE+Messaging, MCSE+Security, MCT...

Answer #9    Answered By: Leeann Hull     Answered On: May 02


Let me know what else might be useful to add.

Didn't find what you were looking for? Find more on Using Time in SharePoint Or get search suggestion and latest updates.