Strange question on Macro??

classic Classic list List threaded Threaded
5 messages Options
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

Strange question on Macro??

I've been trying to take a formula that calculates the difference between to
dates into years/months/days.The original formula works fine.


Wanted to make a macro or function that would make modifications to
formula to new date cells. Didn't get anything to work with functions, but
have gone thru about 20 macros versions and have something that works,
but don't understand why the final step is required?? Having to manual enter
space and enter after doing an edit? Do it in record, but it isn't recorded??


Original Formula
=IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1
Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 Day,DATEDIF(A2,A1,md)Days),)


Just calculates years months and days. If values are 0, they are not include,
and if singular changes label. Very basic.


Issue was if wanting to use it on other cells, had to do copy to keep address
matching, or had to paste and manually change all the addresses.


Have this latest version of macro that works with issues.
I've got it to ask for the new ranges and then it modifies the formula.
Have tried to combine steps, but always getting message that formula is
wrong, and the correction it gives doesn't work. Issue with all thes??
It places the original formula in cell, and the replaces the value, but cell
doesn't record it as a formula unless I edit the cell, and then have to press a
space or something and then enter?? Just enter doesn't do anything??


Any ideals?? Not a big deal, just something interesting??
Thanks


sub ymdzz1
rem ----------------------------------------------------------------------
rem define variables
dim documentas object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService(com.sun.star.frame.DispatchHelper)


dim sText
sText = InputBox (Please enter Date Cells Example D2,D1:)


rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name =StringName
args2(0).Value ==IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1
Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 Day,DATEDIF(A2,A1,md)Days),)


dispatcher.executeDispatch(document,.uno:EnterString,, 0, args2())
rem ----------------------------------------------------------------------
dim args3(1) as new com.sun.star.beans.PropertyValue
args3(0).Name =By
args3(0).Value = 1
args3(1).Name =Sel
args3(1).Value = false


dispatcher.executeDispatch(document,.uno:GoDown,, 0, args3())


rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name =StringName
args4(0).Value ==SUBSTITUTE(FORMULA(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),A2,A1,sText)
dispatcher.executeDispatch(document,.uno:EnterString,, 0, args4())
dispatcher.executeDispatch(document,.uno:Copy,, 0, Array())


rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name =By
args5(0).Value = 1
args5(1).Name =Sel
args5(1).Value = false


dispatcher.executeDispatch(document,.uno:GoDown,, 0, args5())


rem ----------------------------------------------------------------------
dim args6(5) as new com.sun.star.beans.PropertyValue
args6(0).Name =Flags
args6(0).Value =S
args6(1).Name =FormulaCommand
args6(1).Value = 0
args6(2).Name =SkipEmptyCells
args6(2).Value = false
args6(3).Name =Transpose
args6(3).Value = false
args6(4).Name =AsLink
args6(4).Value = false
args6(5).Name =MoveMode
args6(5).Value = 4


dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args6())


rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document,.uno:SetInputMode,, 0, Array())


rem Have to have edit above then space then enter??




end sub




--
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 Tiernan Michael Tiernan
Reply | Threaded
Open this post in threaded view
|

Re: Strange question on Macro?? (in Spreadsheet)

On 7/2/20 2:06 PM, Michael D. Setzer II wrote:
> I've been trying to take a formula that calculates the difference between to
> dates into years/months/days.

I'll ask if anyone has a macro or formulae that would do almost the same
thing but allow it to be expressed further out to include "hours" and
"minutes" I'd like to see it if possible.

I have a running log of incidents and it has two sets of columns.

First pair is "Start" and "End" of power event. That calculation would
be nice if I could express it as:

### Days, ## Hours, ## Minutes

The second calculation, the one that I've been working on is the
difference between the previous "End" and the new "Start" and that I do
need to express "[# Year][# Months][## Days][## Hours] ## Minutes" where
the values of '0' wouldn't be shown (but that's a luxury right now)

The 'datedif' function would work if it accepted the arguments for
smaller measures such as "h"/"m"/"s".

Any suggestions?

--
   << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan
   Non Impediti Ratione Cogatationis
   Women and cats will do as they please, and men and dogs
    should relax and get used to the idea. -Robert A. Heinlein


--
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
Jean-Francois Nifenecker Jean-Francois Nifenecker
Reply | Threaded
Open this post in threaded view
|

Re: Strange question on Macro?? (in Spreadsheet)

Hi Michael,

Le 07/07/2020 à 14:34, Michael Tiernan a écrit :

> On 7/2/20 2:06 PM, Michael D. Setzer II wrote:
>> I've been trying to take a formula that calculates the difference
>> between to
>> dates into years/months/days.
>
> I'll ask if anyone has a macro or formulae that would do almost the same
> thing but allow it to be expressed further out to include "hours" and
> "minutes" I'd like to see it if possible.
>
> I have a running log of incidents and it has two sets of columns.
>
> First pair is "Start" and "End" of power event. That calculation would
> be nice if I could express it as:
>
> ### Days, ## Hours, ## Minutes
>
> The second calculation, the one that I've been working on is the
> difference between the previous "End" and the new "Start" and that I do
> need to express "[# Year][# Months][## Days][## Hours] ## Minutes" where
> the values of '0' wouldn't be shown (but that's a luxury right now)
>
> The 'datedif' function would work if it accepted the arguments for
> smaller measures such as "h"/"m"/"s".
>
> Any suggestions?
>

Use the DateDiff() function with the "s" (seconds) parameter. Then,
divide the result with the appropriate value to get hours, minutes and
remainder seconds.

Below is a function of mine that I use for logging purposes ;)

8< --------------------------------------------------------

Function _GetDurationStr(ByVal pSeconds As Long, Optional pFmt As
Variant) As String
'Returns the duration as a string, with days, minutes and seconds numbers.
'Input:
'-- pSeconds: the number of seconds to translate.
'-- pFmt: (optional) an array of strings symbols for days, minutes and
seconds.
'         Defaults to none (local representation)
'         pFmt is supposed to be an array or 4 strings: day, hr, min and
sec symbols, in that order.
'         Ex: Array("D", "H", "m", "s")
'         Whenever a symbol is left empty, it is replaced with the
default symbol.
'         The default symbols are: "days", "hrs", "min" and "s".
'Output: a string of values and symbols for a duration.

        Const SECONDS_IN_DAY    = 86400
     Const SECONDS_IN_HOUR   = 3600
     Const SECONDS_IN_MINUTE = 60
     'Const SECONDS_IN_WEEK   = 604800 'not used

        Dim l_Sec As Long 'values
        Dim l_Min As Long
        Dim l_Hrs As Long
        Dim l_Days As Long
        Dim l_SecSym As String 'symbols
        Dim l_MinSym As String
        Dim l_HrSym As String
        Dim l_DaySym As String
        Dim l_Str As String 'output
       
        l_Sec = Abs(pSeconds)

        'set symbols
        If Not IsMissing(pFmt) Then
                l_DaySym = pFmt(0)
                l_HrSym  = pFmt(1)
                l_MinSym = pFmt(2)
                l_SecSym = pFmt(3)
        End If

        If (l_DaySym = "") Then l_DaySym = "days"
        If (l_HrSym = "")  Then l_HrSym  = "hrs"
        If (l_MinSym = "") Then l_MinSym = "min"
        If (l_SecSym = "") Then l_SecSym = "s"

        'compute values
        l_Days = l_Sec \ SECONDS_IN_DAY
        l_Hrs = (l_Sec MOD SECONDS_IN_DAY) \ SECONDS_IN_HOUR
        l_Min = ((l_Sec MOD SECONDS_IN_DAY) MOD SECONDS_IN_HOUR) \
SECONDS_IN_MINUTE
        l_Sec = ((l_Sec MOD SECONDS_IN_DAY) MOD SECONDS_IN_HOUR) MOD
SECONDS_IN_MINUTE
       
        'create the output string
        If (l_Days > 0) Then l_Str = l_Str & CStr(l_Days) & " " & l_DaySym & " "
        If (l_Hrs > 0)  Then l_Str = l_Str & CStr(l_Hrs) & " " & l_HrSym & " "
        If (l_Min > 0)  Then l_Str = l_Str & CStr(l_Min) & " " & l_MinSym & " "
        If (l_Sec > 0) Or (pSeconds = 0) Then l_Str = l_Str & CStr(l_Sec) & " "
& l_SecSym
       
        'remove the possible trailing space
        If (Right(l_Str, 1) = " ") Then
                l_Str = Left(l_Str, Len(l_Str) - 1)
        End If

        _GetDurationStr = l_Str
End Function '_GetDurationStr

-------------------------------------------------------- >8


HTH,
--
Jean-Francois Nifenecker, Bordeaux


--
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: Strange question on Macro?? (in Spreadsheet)

In reply to this post by Michael Tiernan
Did a test with the following:
In cell A1 =int(now())
in cell A2 =A1+time(1,12,0)
In cell B2 =Datedif($a$1,a2,d)'get days diff
in cellC2 =A2-$A$1'gets HH:MM:SS but hours is total
in cell D2 = (A2-$A$1)-int(a2-$a$1)'gets the hours minutes seconds
in cell E2
=DATEDIF($A$1,A2,d)daysTEXT((A2-$A$1)-INT(A2-$A$1),HhoursMminutes)


Result is like
0 days 1 hours 12 minutes
in e155 after copying7 days 16 hours 48 minutes


Copied A2-E2 to A3 down to E155


The formula in E2 might do what you want??
Did have it in F2 with the additions of seconds, but found the numbers would
get off at points with a second. Probable the rounding factor of second
portion??


Hope that gives and ideal.Note thespacein E2 formula.


Good Luck.




On 7 Jul 2020 at 8:34, Michael Tiernan wrote:


From:Michael [hidden email]
Subject:Re: [libreoffice-users] Strange question on
Macro?? (in Spreadsheet)
To:[hidden email]
Date sent:Tue, 7 Jul 2020 08:34:38 -0400


On 7/2/20 2:06 PM, Michael D. Setzer II wrote:
I've been trying to take a formula that calculates the difference between to
dates into years/months/days.


I'll ask if anyone has a macro or formulae that would do almost the same
thing but allow it to be expressed further out to includehoursand
minutesI'd like to see it if possible.


I have a running log of incidents and it has two sets of columns.


First pair isStartandEndof power event. That calculation would
be nice if I could express it as:


### Days, ## Hours, ## Minutes


The second calculation, the one that I've been working on is the
difference between the previousEndand the newStartand that I do
need to express[# Year][# Months][## Days][## Hours] ## Minuteswhere
the values of '0' wouldn't be shown (but that's a luxury right now)


The 'datedif' function would work if it accepted the arguments for
smaller measures such ash/m/s.


Any suggestions?


--
MCTMichael C Tiernan. http://www.linkedin.com/in/mtiernan
Non Impediti Ratione Cogatationis
Women and cats will do as they please, and men and dogs
should relax and get used to the idea. -Robert A. Heinlein




--
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: Strange question on Macro?? (in Spreadsheet)

Not sure what happened to the post?? Just saw it on lists, and all the quotes
where somehow removed?


In the B2 line the d is suppose to be in double quotes?
Here I took the info, and removed the comments, and changed the quotes to
~ characters. It also removed the spaces??


Did a test with the following:
In cell A1 =int(now())
in cell A2 =A1+time(1,12,0)
In cell B2 =Datedif($a$1,a2,~d~)f
in cellC2 =A2-$A$1
in cell D2 = (A2-$A$1)-int(a2-$a$1)
in cell E2
=DATEDIF($A$1,A2,~d~)~ days ~TEXT((A2-$A$1)-INT(A2-$A$1),~ H
~~hours~~ M ~~minutes ~~~)


If spaces are removed again, this is the line with spaces replaced with _
=DATEDIF($A$1,A2,~d~)~_days_~TEXT((A2-$A$1)-INT(A2-$A$1),~_H_~~hours~~_M_~~minutes_~~__~)




On 8 Jul 2020 at 3:12, Michael D. Setzer II wrote:


From:Michael D. Setzer [hidden email]
To:Michael [hidden email],
[hidden email]
Date sent:Wed, 08 Jul 2020 03:12:21 +1000
Subject:Re: [libreoffice-users] Strange question on
Macro?? (in Spreadsheet)
Priority:normal


Did a test with the following:
In cell A1 =int(now())
in cell A2 =A1+time(1,12,0)
In cell B2 =Datedif($a$1,a2,d)'get days diff
in cellC2 =A2-$A$1'gets HH:MM:SS but hours is total
in cell D2 = (A2-$A$1)-int(a2-$a$1)'gets the hours minutes seconds
in cell E2
=DATEDIF($A$1,A2,d)daysTEXT((A2-$A$1)-INT(A2-$A$1),HhoursMminutes)




Result is like
0 days 1 hours 12 minutes
in e155 after copying7 days 16 hours 48 minutes




Copied A2-E2 to A3 down to E155




The formula in E2 might do what you want??
Did have it in F2 with the additions of seconds, but found the numbers would
get off at points with a second. Probable the rounding factor of second
portion??




Hope that gives and ideal.Note thespacein E2 formula.




Good Luck.




On 7 Jul 2020 at 8:34, Michael Tiernan wrote:




From:Michael [hidden email]
Subject:Re: [libreoffice-users] Strange question on
Macro?? (in Spreadsheet)
To:[hidden email]
Date sent:Tue, 7 Jul 2020 08:34:38 -0400




On 7/2/20 2:06 PM, Michael D. Setzer II wrote:
I've been trying to take a formula that calculates the difference between to
dates into years/months/days.




I'll ask if anyone has a macro or formulae that would do almost the same
thing but allow it to be expressed further out to includehoursand
minutesI'd like to see it if possible.




I have a running log of incidents and it has two sets of columns.




First pair isStartandEndof power event. That calculation would
be nice if I could express it as:




### Days, ## Hours, ## Minutes




The second calculation, the one that I've been working on is the
difference between the previousEndand the newStartand that I do
need to express[# Year][# Months][## Days][## Hours] ## Minuteswhere
the values of '0' wouldn't be shown (but that's a luxury right now)




The 'datedif' function would work if it accepted the arguments for
smaller measures such ash/m/s.




Any suggestions?




--
MCTMichael C Tiernan. http://www.linkedin.com/in/mtiernan
Non Impediti Ratione Cogatationis
Women and cats will do as they please, and men and dogs
should relax and get used to the idea. -Robert A. Heinlein




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