2018 Archives by date, by thread · List index

# 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")

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

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