2020 Archives by date, by thread · List index

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