Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index

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

Jean-Francois Nifenecker, Bordeaux

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.