Re: [libreoffice-users] 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: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy