Calculate difference between two dates (with time)

users
#1

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?

Thanks,

Kruno

#2

01/17/18 03:30 PM 01/17/18 03:30 PM
01/17/18 10:20 PM 01/17/18 10:20 PM
0:6:50:0 0:06:50:00
Formula in A3
"=TEXT(INT(A2-A1),"#0")&":"&TEXT(((A2-A1)-INT(A2-A1)),"H:M:S")

Formula in B3
"=TEXT(INT(A2-A1),"#0")&":"&TEXT(((A2-A1)-INT(A2-A1)),"HH:MM:SS")

I did include seconds, but you could remove the :S, and have options to
show 2 digits or just one. The #0 makes it print the 0, but ## would print
blank for zero values.

#3

12.07.2018 u 16:36, Michael D. Setzer II je napisao/la:

01/17/18 03:30 PM 01/17/18 03:30 PM
01/17/18 10:20 PM 01/17/18 10:20 PM
0:6:50:0 0:06:50:00
Formula in A3
"=TEXT(INT(A2-A1),"#0")&":"&TEXT(((A2-A1)-INT(A2-A1)),"H:M:S")

Nice! Thanks!

But makes me wonder what's wrong with

=TEXT((A2-A1);"d:h:m")

as it works well in Excel. Maybe I should file a feature request?

Kruno

#4

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.

#5

Hi Krunose,

just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18.
22:20, why then

Did you type it right here? Point after 18? Dont know if this is a right
format for a date/time-value.

I have tried it with German date-time-values and have set only
=A2 - A1
... and it returns 06:50:00

Regrads

Robert

#6

between two dates (with
  time)
Date sent: Fri, 13 Jul 2018 07:56:17 +0200

Hi Krunose,
>
> just a quick question: if A1 holds 1.7.18. 15:30 and if A2 holds 1.7.18.
> 22:20, why then

Did you type it right here? Point after 18? Dont know if this is a right
format for a date/time-value.

I have tried it with German date-time-values and have set only
=A2 - A1
... and it returns 06:50:00

Regrads

Robert

That would give difference in hours only, but would not give the days
This is cells L1-M7 Right of ___ is column M contents.

01/17/18 03:30 PM
01/28/18 12:20 PM
10:20:50:0______"=TEXT(INT(L2-L1),"#0")&":"&TEXT(((L2-L1)-INT(L2-L1)),"
H:M:S")
08:50:00 PM_____________ L2-L1 Format Time/AM/PM
20:50:00________________ L2-L1 Format Hour Minute Secound
17______________________ "=INT(L1)-DATE(YEAR(L1),1,1)+1
28______________________ "=INT(L1)-DATE(YEAR(L1),1,1)+1

I also entered dates as the cells L1 and L2 shows, but did use the 24 hour
format for the time, but it changed to AM/PM format by default. Some locals
use the . instead of /, so whatever matches with setup.

Added the Julian Date calculation did earlier, but modified to use int, since L1
and L2 have a time value included.

#7

Hi michael,

at first: Why do you answer to the list, some other persons and my
private mail-address? I am reading and writing the the list and doens#T
need the same message twice.

I have tried it with German date-time-values and have set only
=A2 - A1
... and it returns 06:50:00

Regrads

Robert

That would give difference in hours only, but would not give the days
This is cells L1-M7 Right of ___ is column M contents.

OK, need the day as a separate value:
=ABRUNDEN(B1-A1)&":"&TEXT(B1-A1;"HH:MM")

ABRUNDEN is the German Name for the function. Could be it is named FLOOR
in the English version.

HH:MM gives the hours and minutes without the days.

Regards

Robert

#8

Hi :slight_smile:
It is the design of the mailing list. It's designed so that people can't
just "reply to" in order to reply to the whole mailing list. Clicking on
"reply to" makes the reply private so that it can't be seen by the mailing
list as a whole.

The only option offered by most email clients is the "Reply to all". Some
specialist and rarely used email clients do offer a "Reply to group" but
that is rarely needed for any other mailing list or anywhere so people are
not used to using it, even if they are lucky enough to have the option
available.

This problem is compounded by the "Reply to all" putting the mailing list's
address in the "CC" field instead of the "To". This means that almost
everyone is supposed to perform some dexterous cut&pasting for every single
reply which is a real pita on a hand-held device and is not entirely easy
on the most commonly used email clients.

I think a lot of us have complained to the people who carefully set-up the
mailing list to misbehave in this awkward and clumsy fashion but they claim
that the majority of people prefer it this way.

So we have had to learn to just delete twice or answer once and delete the
second one or some-such. Hitting the delete key really not that arduous
surely?
Regards from
a Tom :slight_smile:

#9

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

#10

Hi :slight_smile:
It is the design of the mailing list. It's designed so that people
can't just "reply to" in order to reply to the whole mailing list.
Clicking on "reply to" makes the reply private so that it can't be
seen by the mailing list as a whole.

That's simply not true. I just clicked on Reply to send this message,
and as you can see, it has been sent to the whole list.

You must be using a broken mailer.

#11

Hi :slight_smile:
Wow!! That is the first time anyone has said that on this mailing list
afaik

Are you using Thunderbird or "The Bat!" or what?
Regards from
a Tom :slight_smile:

#12

I'm with Tom on this one. If I click "Reply", it only goes to the individual sender, and not the list. On my phone, I can click "Reply All" as I have just done here, but Tom will get it twice. On my Linux Mint laptop with Thunderbird, I have a "Reply to List" option that only sends it to the list.

Virgil

#13

So use Thunderbird on Mint and stop using your phone. Or take the
trouble to edit recipients on your phone, or get a better mail app on
your phone.

FWIW, I use claws, which is available for a range of systems.

Mails from this list include this header:

List-Post: <mailto:users@global.libreoffice.org>

and that tells your mail program that it should reply to the list. So
the list is not broken, your mailer is if it doesn't.

Oh, and please do take the trouble to NOT send me an extra copy if you
want me not to block mails from you.

#14

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

between two dates time)
Date sent:60;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</span>

=3DTEXT((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...

Little bit complicated. If values of later items are smaller it gets more
complex.
=year(a2)-year(a1) would give years, but if month of a1mount a2
In just starting, this handles it for just the month being less, but day and other
values would also need to be worked for negative result.=YEAR(L14)-YEAR(L13)-(MONTH(L14)MONTH(L13))

Then you have the issue of different days in months, and leap years that change number of
days in February.

Thanks,

Kruno

#15

Chill, Dave

#16

Goodbye, Virgil.

#17

So use Thunderbird on Mint and stop using your phone. Or take the
trouble to edit recipients on your phone, or get a better mail app on
your phone.

FWIW, I use claws, which is available for a range of systems.

Mails from this list include this header:

List-Post: <mailto:users@global.libreoffice.org>

and that tells your mail program that it should reply to the list. So
the list is not broken, your mailer is if it doesn't.

Even though Dave has bid me farewell, I do want to respond more fully
for the benefit of the list.

Tom had very accurately described the behavior most of us seem to
experience daily with the LO mailing list, that hitting "Reply"
typically responds to the original sender while hitting "Reply All"
responds to original sender with a Cc to the list. Thunderbird has added
a "Reply List" button which is helpful in addressing this type of behavior.

Dave responded to Tom claiming that Tom's description of the design of
the LO list simply isn't true and that any email client that behaves as
Tom described is "broken." When I agreed with Tom description, Dave
advised me that, if my mailer doesn't display the header as his does, it
is because my mailer is "broken." I have been on the LO mailing list for
many years and none of the mailers I have used, whether under Windows,
Linux, or Android, have displayed the header as Dave describes. Rather,
they all display it as seen in my response to Tom below:

From: Tom Davies <tomcecf@gmail.com>
Date: 7/13/18 9:55 AM (GMT-06:00)
To: Dave Howorth <dave@howorth.org.uk>
Cc: users@global.libreoffice.org
Subject: Re: [libreoffice-users] Calculate difference between two
dates (with time)

You'll see that, even though I received the email from the LO list, the
header states that it was sent from Tom to Dave, with a Cc to the list.

Now, apparently Dave has found a mailer that behaves differently and I'm
happy for him. Of course, that doesn't mean that any program that
doesn't behave as his does is "broken." That's the M$ mentality,
insisting that the whole world comply with its way of working no matter
if it is unique.

I think we can all agree the LO list behaves differently than other
email lists. I have belonged to many other email lists and they all --
with the sole exception of the LO list -- reply directly to the list by
hitting "Reply." Based on posts that I have read on this list over many
years, it appears that this is the most common behavior with the
majority of LO list users across a wide variety of email client
software. Like Tom, I have never seen a post that claimed, as Dave did,
that hitting "Reply" responds directly to the list.

Obviously there is a disconnect somewhere. Since other mailing lists are
designed in such a way that hitting "Reply" responds to the list on all
email clients, it seems that the LO list could likewise be so designed.
Tom has suggested that the LO list designers have deliberately chosen
not to design it that way because most users don't *want* to respond to
the list by clicking "Reply." Dave has responded by saying that the LO
list is behaving just fine and everybody else should just use a
different mailer. He even ordered me to stop using my phone to respond
to LO emails.

So, assuming both Tom and Dave are accurately describing the behavior of
the LO list on their respective software, there a several possible
solutions.

1. The LO list could be redesigned to behave like other mailing lists so
that hitting "Reply" responds to the list, not the individual sender.
2. The many, many, many LO list members having to hit "Reply all" could
all discard their favorite email clients because they are obviously
"broken."
3. Users writing a response by hitting "Reply all" could delete the
individual sender and then move the LO list from the "Cc:" line to the
"To:" line.
4. The recipient of double messages could hit "Delete" to get rid of the
extra message.

One thing I have learned in my six plus decades of life is that there is
little to be gained from trying to order and direct the behavior of
others. As a recipient of LO list emails, solution #4 is the only one
that lies completely within my control. I can't make the LO list
designers change the list design. Try as he might, Dave can't make all
of the list members change their email client software, nor can we force
each other to take the time to edit their header to ensure that double
emails aren't sent. If I don't want to receive double emails, all I can
do is hit "Delete."

I can also do as Dave did and block all LO list members who send double
emails, but that seems counterproductive. If one blocks all of the LO
list users who "Reply all" without deleting the individual from the
"To:" line, then s/he might miss out on some helpful information about
LO in future emails. I assume we have joined this list to learn more
about the program and become more proficient with it. If we just block
each other because we're offended at having to hit "Delete" one extra
time, then we may be cutting off our nose to spite our face.

Virgil

#18

Hi :slight_smile:
+1

This mailing list is a gateway from the Microsquish world so we have got to
be able to handle whatever systems MS users are using at any point in
time. We cannot expect them to magically know all our individual pet
hates. We cannot expect them to change ALL their systems at the same time
just in order to be able to communicate with us.

We cannot be unwelcoming and petulant because many people have stayed away
from linux for exactly that reason. Various projects such as Ubuntu
started using a "Code of Conduct" to welcome people in One of the crucial
jobs of this mailing list is to be welcoming instead of judgmental.

Regards from
a Tom :slight_smile:

#19

Hi :slight_smile:
+1

This mailing list is a gateway from the Microsquish world so we have got to
be able to handle whatever systems MS users are using at any point in
time. We cannot expect them to magically know all our individual pet
hates. We cannot expect them to change ALL their systems at the same time
just in order to be able to communicate with us.

We cannot be unwelcoming and petulant because many people have stayed away
from linux for exactly that reason. Various projects such as Ubuntu
started using a "Code of Conduct" to welcome people in One of the crucial
jobs of this mailing list is to be welcoming instead of judgmental.

Regards from
a Tom :slight_smile:

#20

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.

between two dates time)
Date sent: Fri, 13 Jul 2018 15:11:58 +0200