Re: [libreoffice-users] difference in hours between two times

Since the list doesn't like posting of data directly from spreadsheet format.
Did this testing, and saved as a CSV file.

Start Date,Start Time
05/16/18,08:30:00 PM
,
End Date,End Time
05/21/18,12:05:00 PM
,
Calc Hours,
111.58,"'=((A5+B5)-(A2+B2))/TIME(1,0,0)"
,
Calc Day,
4.65,'=A8/24
,
Calc Whole Days,
4,'=INT(A11)
,
Calc Leftover Time Method 1,
15:35,'=A11-A14 (User defined format HH:MM for Cell)
,
Calc Leftover Time Method 2,
15:35,"'=MOD(A11,1) (User defined format HH:MM for Cell)"
,
Create String of Result,
4 Days 15:35,"'=TEXT(INT(A11),""##"")&"" Days 
""&TEXT(MOD(A11,1),""HH:MM"")"
,
Single String Formula,
4 Days 15:35,"'=TEXT(INT(((A5+B5)-(A2+B2))/TIME(1,0,0)/24),""##"")&"" 
Days ""&TEXT(MOD(((A5+B5)-(A2+B2))/TIME(1,0,0)/24,1),""HH:MM"")"


Last formula only needs the one cell, but if it is just to display, looks nice. 

On 25 May 2018 at 1:21, James wrote:

From:                   James <bjlockie@lockie.ca>
To:                     "Michael D. Setzer II" <msetzerii@gmail.com>
Date sent:              Fri, 25 May 2018 01:21:14 +0000 (UTC)
Subject:                Re: [libreoffice-users] difference in hours between 
two times

> Thanks
>
> On May 24, 2018 6:05:24 PM "Michael D. Setzer II" <msetzerii@gmail.com> wrote:
>
> > Divide the difference by an hour, and you get the hours, and then divide that
> > by 24 or could do the same and divide by 24 hours.
> >
> >
> > 05/16/18    08:30:00 PM        111.58                                               4.65
> > 05/21/18    12:05:00 PM        '=((A2+B2)-(A1+B1))/TIME(1,0,0)      '=C1/24
> >
> >
> > On 24 May 2018 at 15:07, James wrote:
> >
> > To:                 users@global.libreoffice.org
> > From:               James <bjlockie@lockie.ca>
> > Subject:            [libreoffice-users] difference in hours between two
> > times
> > Date sent:          Thu, 24 May 2018 15:07:15 -0400
> >
> > > I have a calc spreadsheet with column A being a date and column B is a time.
> > > I am trying to make a foruma that calculates the difference in days and
> > > another for the difference in hours between 2 rows.
> > >
> > > For example:
> > > column A
> > > Wed, May 16, 2018
> > > Mon, May 21, 2018
> > >
> > > column B
> > > 08:30 PM   
> > > 12:05 PM
> > >
> > > column C
> > > =((A13+B13)-(A12+B12))
> > > =((A14+B14)-(A13+B13))[4.65]
> > >
> > > column D
> > > =TRUNC(C13)
> > > =TRUNC(C14) [4]
> > >
> > > column E
> > > -
> > > formula? [.65]
> > >
> > >
> > > --
> > > To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> > > Problems? 
> > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> > > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
> > > List archive: https://listarchives.libreoffice.org/global/users/
> > > Privacy Policy: https://www.documentfoundation.org/privacy
> >
> >
> > +------------------------------------------------------------+
> > Michael D. Setzer II - Computer Science Instructor (Retired)
> > mailto:mikes@guam.net
> > mailto:msetzerii@gmail.com
> > Guam - Where America's Day Begins
> > G4L Disk Imaging Project maintainer
> > http://sourceforge.net/projects/g4l/
> > +------------------------------------------------------------+
> >
> > http://setiathome.berkeley.edu (Original)
> > Number of Seti Units Returned:  19,471
> > Processing time:  32 years, 290 days, 12 hours, 58 minutes
> > (Total Hours: 287,489)
> >
> > BOINC@HOME CREDITS
> >
> > ROSETTA      65540984.279607 | ABC          16613838.513356
> > SETI        109371053.691072 | EINSTEIN    141058465.999240


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)     
 mailto:mikes@guam.net                            
 mailto:msetzerii@gmail.com
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer 
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+

http://setiathome.berkeley.edu (Original)
Number of Seti Units Returned:  19,471
Processing time:  32 years, 290 days, 12 hours, 58 minutes
(Total Hours: 287,489)

BOINC@HOME CREDITS

ROSETTA      65544223.144121 | ABC          16613838.513356
SETI        109372067.673681 | EINSTEIN    141058465.999240


-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy