Calculate difference between two dates (with time)

classic Classic list List threaded Threaded
21 messages Options
Next » 12
Krunose Krunose
Reply | Threaded
Open this post in threaded view
|

Calculate difference between two dates (with time)

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


--
To unsubscribe e-mail to: [hidden email]
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 Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

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.


On 12 Jul 2018 at 13:42, Krunose wrote:

To:             [hidden email]
From:           Krunose <[hidden email]>
Subject:         [libreoffice-users] Calculate difference between
two dates (with time)
Date sent:       Thu, 12 Jul 2018 13:42:36 +0200

> 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
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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:[hidden email]                            
 mailto:[hidden email]
 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      65602903.467560 | ABC          16613838.513356
SETI        109419383.050719 | EINSTEIN    141198856.499240


--
To unsubscribe e-mail to: [hidden email]
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

Krunose Krunose
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

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


>
> 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.
>
>
> On 12 Jul 2018 at 13:42, Krunose wrote:
>
> To:             [hidden email]
> From:           Krunose <[hidden email]>
> Subject:         [libreoffice-users] Calculate difference between
> two dates (with time)
> Date sent:       Thu, 12 Jul 2018 13:42:36 +0200
>
>> 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
>>
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> 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:[hidden email]
>   mailto:[hidden email]
>   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      65602903.467560 | ABC          16613838.513356
> SETI        109419383.050719 | EINSTEIN    141198856.499240
>


--
To unsubscribe e-mail to: [hidden email]
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
libreoffice-ml.mbourne libreoffice-ml.mbourne
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates time)

In reply to this post by Krunose
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.

--
Mark.


--
To unsubscribe e-mail to: [hidden email]
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
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

In reply to this post by Krunose
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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
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 Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

On 13 Jul 2018 at 7:56, Robert Großkopf wrote:

Subject:         Re: [libreoffice-users] Calculate difference
between two dates (with
        time)
To:             [hidden email]
From:           Robert Großkopf <[hidden email]>
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.



+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 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      65604277.831505 | ABC          16613838.513356
SETI        109420652.286057 | EINSTEIN    141202628.499240


--
To unsubscribe e-mail to: [hidden email]
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

Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
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
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

Hi :)
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 :)






On 13 July 2018 at 13:48, Robert Großkopf <[hidden email]>
wrote:

> 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
> --
> Homepage: http://robert.familiegrosskopf.de
> LibreOffice Community: http://robert.familiegrosskopf.de/map_3
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>

--
To unsubscribe e-mail to: [hidden email]
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
Krunose Krunose
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates time)

In reply to this post by libreoffice-ml.mbourne
12.07.2018 u 22:52, [hidden email] 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: [hidden email]
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
Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

In reply to this post by TomD
On Fri, 13 Jul 2018 14:10:38 +0100
Tom Davies <[hidden email]> wrote:

> Hi :)
> 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.

> 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 :)
>
>
>
>
>
>
> On 13 July 2018 at 13:48, Robert Großkopf <[hidden email]>
> wrote:
>
> > 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
> > --
> > Homepage: http://robert.familiegrosskopf.de
> > LibreOffice Community: http://robert.familiegrosskopf.de/map_3
> >
> >
> > --
> > To unsubscribe e-mail to: [hidden email]
> > 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 
>


--
To unsubscribe e-mail to: [hidden email]
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
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

Hi :)
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 :)




On 13 July 2018 at 15:13, Dave Howorth <[hidden email]> wrote:

> On Fri, 13 Jul 2018 14:10:38 +0100
> Tom Davies <[hidden email]> wrote:
>
> > Hi :)
> > 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.
>
> > 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 :)
> >
> >
> >
> >
> >
> >
> > On 13 July 2018 at 13:48, Robert Großkopf <[hidden email]>
> > wrote:
> >
> > > 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
> > > --
> > > Homepage: http://robert.familiegrosskopf.de
> > > LibreOffice Community: http://robert.familiegrosskopf.de/map_3
> > >
> > >
> > > --
> > > To unsubscribe e-mail to: [hidden email]
> > > 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
> >
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>

--
To unsubscribe e-mail to: [hidden email]
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
Virgil Arrington Virgil Arrington
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

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


Sent from my Verizon, Samsung Galaxy smartphone


-------- Original message --------
From: Tom Davies <[hidden email]>
Date: 7/13/18 9:55 AM (GMT-06:00)
To: Dave Howorth <[hidden email]>
Cc: [hidden email]
Subject: Re: [libreoffice-users] Calculate difference between two dates (with time)

Hi :)
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 :)




On 13 July 2018 at 15:13, Dave Howorth <[hidden email]> wrote:

> On Fri, 13 Jul 2018 14:10:38 +0100
> Tom Davies <[hidden email]> wrote:
>
> > Hi :)
> > 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.
>
> > 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 :)
> >
> >
> >
> >
> >
> >
> > On 13 July 2018 at 13:48, Robert Großkopf <[hidden email]>
> > wrote:
> >
> > > 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
> > > --
> > > Homepage: http://robert.familiegrosskopf.de
> > > LibreOffice Community: http://robert.familiegrosskopf.de/map_3
> > >
> > >
> > > --
> > > To unsubscribe e-mail to: [hidden email]
> > > 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
> >
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>

--
To unsubscribe e-mail to: [hidden email]
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

--
To unsubscribe e-mail to: [hidden email]
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

Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

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:[hidden email]>

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.

On Fri, 13 Jul 2018 15:05:08 +0000
Virgil Arrington <[hidden email]> wrote:

> 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
>
>
> Sent from my Verizon, Samsung Galaxy smartphone
>
> -------- Original message --------
> From: Tom Davies <[hidden email]>
> Date: 7/13/18 9:55 AM (GMT-06:00)
> To: Dave Howorth <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [libreoffice-users] Calculate difference between two
> dates (with time)
>
> Hi :)
> 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 :)
>
>
>
>
> On 13 July 2018 at 15:13, Dave Howorth <[hidden email]> wrote:
>
> > On Fri, 13 Jul 2018 14:10:38 +0100
> > Tom Davies <[hidden email]> wrote:
> >  
> > > Hi :)
> > > 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.
> >  
> > > 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 :)

--
To unsubscribe e-mail to: [hidden email]
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 Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates time)

In reply to this post by Krunose
=On 13 Jul 2018 at 15:11, Krunose wrote:


Subject:;Re: [libreoffice-users] Calculate difference
between two dates time)
To:;[hidden email]
From:60;[hidden email]
Date sent:60;Fri, 13 Jul 2018 15:11:58 +0200


12.07.2018 u 22:52, [hidden email] 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




--
To unsubscribe e-mail to: [hidden email]
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




--
To unsubscribe e-mail to: [hidden email]
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

Virgil Arrington Virgil Arrington
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

In reply to this post by Dave Howorth
Chill, Dave



Sent from my Verizon, Samsung Galaxy smartphone


-------- Original message --------
From: Dave Howorth <[hidden email]>
Date: 7/13/18 11:59 AM (GMT-06:00)
To: [hidden email]
Subject: Re: [libreoffice-users] Calculate difference between two dates (with time)

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:[hidden email]>

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.

On Fri, 13 Jul 2018 15:05:08 +0000
Virgil Arrington <[hidden email]> wrote:

> 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
>
>
> Sent from my Verizon, Samsung Galaxy smartphone
>
> -------- Original message --------
> From: Tom Davies <[hidden email]>
> Date: 7/13/18 9:55 AM (GMT-06:00)
> To: Dave Howorth <[hidden email]>
> Cc: [hidden email]
> Subject: Re: [libreoffice-users] Calculate difference between two
> dates (with time)
>
> Hi :)
> 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 :)
>
>
>
>
> On 13 July 2018 at 15:13, Dave Howorth <[hidden email]> wrote:
>
> > On Fri, 13 Jul 2018 14:10:38 +0100
> > Tom Davies <[hidden email]> wrote:
> >
> > > Hi :)
> > > 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.
> >
> > > 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 :)

--
To unsubscribe e-mail to: [hidden email]
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


--
To unsubscribe e-mail to: [hidden email]
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

Dave Howorth Dave Howorth
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates (with time)

On Fri, 13 Jul 2018 18:17:47 +0000
Virgil Arrington <[hidden email]> wrote:

> Chill, Dave

Goodbye, Virgil.

--
To unsubscribe e-mail to: [hidden email]
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

Virgil Arrington Virgil Arrington
Reply | Threaded
Open this post in threaded view
|

Reply, Reply all, Reply list, and getting offended (was Calculate difference between two,dates (with time))

In reply to this post by Dave Howorth

On 07/13/2018 12:57 PM, Dave Howorth wrote:

> 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:[hidden email]>
>
> 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:

>> -------- Original message --------
>> From: Tom Davies <[hidden email]>
>> Date: 7/13/18 9:55 AM (GMT-06:00)
>> To: Dave Howorth <[hidden email]>
>> Cc: [hidden email]
>> 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



--
To unsubscribe e-mail to: [hidden email]
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
TomD TomD
Reply | Threaded
Open this post in threaded view
|

Re: Reply, Reply all, Reply list, and getting offended (was Calculate difference between two,dates (with time))

Hi :)
+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 :)





On 13 July 2018 at 23:14, Virgil Arrington <[hidden email]> wrote:

>
> On 07/13/2018 12:57 PM, Dave Howorth wrote:
> > 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:[hidden email]>
> >
> > 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:
>
> >> -------- Original message --------
> >> From: Tom Davies <[hidden email]>
> >> Date: 7/13/18 9:55 AM (GMT-06:00)
> >> To: Dave Howorth <[hidden email]>
> >> Cc: [hidden email]
> >> 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
>
>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>

--
To unsubscribe e-mail to: [hidden email]
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 Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates time)

In reply to this post by Krunose
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:             [hidden email]
From:           Krunose <[hidden email]>
Date sent:       Fri, 13 Jul 2018 15:11:58 +0200

> 12.07.2018 u 22:52, [hidden email] 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: [hidden email]
> 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:[hidden email]                            
 mailto:[hidden email]
 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: [hidden email]
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 Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Re: Calculate difference between two dates time)

I was right about it not handling some dates, but was more complex than I
though. Put in a date that was 1 second short of a year, and it was bad.

This seems to resolve the issue. Might have more rows than needed, Since
sometimes it has the correct results before the final row.
Again, pasted formulas to text editor, and change " " to _ and \t to ;

Date_1;Date_2;Years;Month;Day;Hour;Minute;Second
04/11/1960_07:42:00;04/11/1961_07:41:59;"=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<0,C21-1,C21);"=IF(D21<0,D21+12,D21)-(E21<0);"=IF(E21<0,DA
Y($B$2),E21)-(F21<0);"=IF(F21<0,F21+24,F21)-(G21<0);"=IF(G21<0,G21+6
0,G21);"=IF(H21<0,H21+60,H21)
;;"=IF(D22<0,C22-1,C22);"=IF(D22<0,D22+12,D22)-(E22<0);"=IF(E22<0,DA
Y($B$2),E22)-(F22<0);"=IF(F22<0,F22+24,F22)-(G22<0);"=IF(G22<0,G22+6
0,G22);"=IF(H22<0,H22+60,H22)
;;"=IF(D23<0,C23-1,C23);"=IF(D23<0,D23+12,D23)-(E23<0);"=IF(E23<0,DA
Y($B$2),E23)-(F23<0);"=IF(F23<0,F23+24,F23)-(G23<0);"=IF(G23<0,G23+6
0,G23);"=IF(H23<0,H23+60,H23)
;;"=IF(D24<0,C24-1,C24);"=IF(D24<0,D24+12,D24)-(E24<0);"=IF(E24<0,DA
Y($B$2),E24)-(F24<0);"=IF(F24<0,F24+24,F24)-(G24<0);"=IF(G21<0,G21+6
0,G21);"=IF(H24<0,H24+60,H24)
;;"=IF(D25<0,C25-1,C25);"=IF(D25<0,D25+12,D25)-(E25<0);"=IF(E25<0,DA
Y($B$2),E25)-(F25<0);"=IF(F25<0,F25+24,F25)-(G25<0);"=IF(H21<0,G25-1,
G25);"=IF(H25<0,H25+60,H25)
;;"=IF(D26<0,C26-1,C26);"=IF(D26<0,D26+12,D26)-(E26<0);"=IF(E26<0,DA
Y($B$2),E26)-(F26<0);"=IF(F26<0,F26+24,F26)-(G26<0);"=IF(H22<0,G26-1,
G26);"=IF(H26<0,H26+60,H26)
;;"=IF(D27<0,C27-1,C27);"=IF(D27<0,D27+12,D27)-(E27<0);"=IF(E27<0,DA
Y($B$2),E27)-(F27<0);"=IF(F27<0,F27+24,F27)-(G27<0);"=IF(H23<0,G27-1,
G27);"=IF(H27<0,H27+60,H27)


On 14 Jul 2018 at 13:12, Michael D. Setzer II wrote:

From:           "Michael D. Setzer II" <[hidden email]>
To:             Krunose <[hidden email]>,
              [hidden email]
Date sent:       Sat, 14 Jul 2018 13:12:44 +1000
Subject:         Re: [libreoffice-users] Calculate difference
between two dates time)
Priority:       normal

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

Next » 12