Arithmetic on Times in SharePoint

A note about how to calculate a field as the sum of two times. The catches
are the format of the "Length" field (probably better titled "Duration"), and the use of the "TEXT" function.

------ Forwarded Message
From: Tom <tomsrig@hotmail.com>
Reply-To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Date: Wed, 11 Mar 2009 00:50:43 -0000
To: "sharepointdiscussions@yahoogroups.com"
<sharepointdiscussions@yahoogroups.com>
Subject: [sharepointdiscussions] Re: Useing Time in SharePoint

Hi, Peter,

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 Length] 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 good.

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

Thanks much, Peter

Tom

--- In sharepointdiscussions@yahoogroups.com, Peter Brunone
<peter.brunone@...> wrote:
>
> Hi Tom,
>
> Does the calculated field save if you use this formula? According to the
> reference at
> http://office.microsoft.com/en-us/sharepointtechnology/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
> nomenclature):
>
> =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
> goes.
>
> Cheers,
>
> Peter
>
> On Mon, Mar 9, 2009 at 3:37 PM, Tom <tomsrig@...> wrote:
>
> > Hi, Peter,
> >
> > 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.
> >
> > I'm stumped.
> >
> > --- In sharepointdiscussions@yahoogroups.com, Peter Brunone
> > <peter.brunone@> wrote:
> > >
> > > 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?
> > >
> > > Peter
> > >
> > > On Mon, Mar 9, 2009 at 2:42 PM, Tom <tomsrig@> wrote:
> > >
> > > > Hi, all,
> > > >
> > > > 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.
> > > >
> > > > Any ideas?
> > > >
> > > > Tom
> >
>
>
> [Non-text portions of this message have been removed]
>




------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/sharepointdiscussions/

<*> Your email settings:
Individual Email Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/sharepointdiscussions/join
(Yahoo! ID required)

<*> To change settings via email:
mailto:sharepointdiscussions-digest@yahoogroups.com
mailto:sharepointdiscussions-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
sharepointdiscussions-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/


------ End of Forwarded Message