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