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: [email protected] From: Krunose <[email protected]> Date sent: Fri, 13 Jul 2018 15:11:58 +0200 > 12.07.2018 u 22:52, [email protected] 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: [email protected] > 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:[email protected] mailto:[email protected] 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: [email protected] 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
