Re: [libreoffice-users] Calculate difference between two dates time)

Think this gets the job done as I see it, but it is a somewhat complex process.
Did the work at top of spreadsheet, but copied it a couple times, and then 
copied it to show formulas.

Pasted into text editor, and changed spaces to _ and \t to ;

Date_1;Date_2;Years;Month;Day;Hour;Minute;Second
04/11/1960_07:42:00;07/14/2018_12:58:15;"=YEAR(B21)-YEAR(A21);"=MO
NTH(B21)-MONTH(A21);"=DAY(B21)-DAY(A21);"=HOUR(B21)-HOUR(A21);
"=MINUTE(B21)-MINUTE(A21);"=SECOND(B21)-SECOND(A21)
;;"=IF(D21<1,C21-1,C21);"=IF(D21<0,D21+12,D21);;;;
;;"=IF(D21<1,C21-1,C21);"=IF(E21<0,D22-1,D22);"=IF(E21<0,DAY(B21),E21
);;;
;;"=C23;"=D23;"=IF(F21<0,E23-1,E23);"=IF(F21<0,F21+24,F21);;
;;"=C24;"=D24;"=E24;"=IF(G21<0,F24-1,F24);"=IF(G21<0,G21+60,G21);
;;"=C25;"=D25;"=E25;"=F25;"=IF(H21<0,G25-1,G25);"=IF(H21<0,H21+60,H2
1)

Has 8 columns, and calcs the differences in second row. Other rows adjust 
values if next column resulted in negative value. Probable would need a little 
more work, since if the top value is 0 or 1 in some cases might require further 
changes? Will have to look at it more, but would do most.


On 13 Jul 2018 at 15:11, Krunose wrote:

Subject:                Re: [libreoffice-users] Calculate difference 
between two dates time)
To:                     users@global.libreoffice.org
From:                   Krunose <kruno.se@gmx.com>
Date sent:              Fri, 13 Jul 2018 15:11:58 +0200

> 12.07.2018 u 22:52, libreoffice-ml.mbourne@spamgourmet.com je napisao/la:
> > Krunose wrote:
> > > Hi,
> > >
> > > just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 
> > > 1.7.18. 22:20, why then
> > >
> > >            =TEXT((A2-A1);"d:h:m")
> > >
> > > returns 30:6:50 instead 0:6:50?
> >
> > Assuming those dates are 7th January 2018, since your mail headers 
> > indicate you're using a US locale...
> >
> > 2018-01-07 15:30:00 is represented internally as 43107.645833
> > 2018-01-07 22:20:00 is represented internally as 43107.930556
> >
> > Subtracting those numbers gives 0.284723, which represents the 
> > date/time 1899-12-30 06:50:00
> >
> > The day of month is 30, hence the 30 when that value is represented as 
> > d:h:m.
>
>
> Ah, I get it -- and I don't. If I want 0.284723 to be represented as 
> 'd-h-m', why to treat it as date? Wouldn't it make sense to include a 
> function to LO Calc that could convert that number in desired format in, 
> just guessing, a text type.
>
> So something like =TIMEPASSED(A1;"d:m:s:ms") or 
> =TIMEPASSED(A1;"YEARS-MONTHS-DAYS-HOURS-MINUTES-SECONDS") to get 
> 0-0-0-6-50-0?
>
> Seams not very complicated to add function like that...
>
> Thanks,
>
> Kruno
>
>
> -- 
> 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      65604691.500930 | ABC          16613838.513356
SETI        109422304.545452 | EINSTEIN    141202628.499240


-- 
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