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