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+unsubscr...@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
Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet)
Not sure what happened to the post?? Just saw it on lists, and all the quotes where somehow removed? In the B2 line the d is suppose to be in double quotes? Here I took the info, and removed the comments, and changed the quotes to ~ characters. It also removed the spaces?? Did a test with the following: In cell A1 =int(now()) in cell A2 =A1+time(1,12,0) In cell B2 =Datedif($a$1,a2,~d~)f in cellC2 =A2-$A$1 in cell D2 = (A2-$A$1)-int(a2-$a$1) in cell E2 =DATEDIF($A$1,A2,~d~)~ days ~TEXT((A2-$A$1)-INT(A2-$A$1),~ H ~~hours~~ M ~~minutes ~~~) If spaces are removed again, this is the line with spaces replaced with _ =DATEDIF($A$1,A2,~d~)~_days_~TEXT((A2-$A$1)-INT(A2-$A$1),~_H_~~hours~~_M_~~minutes_~~__~) On 8 Jul 2020 at 3:12, Michael D. Setzer II wrote: From:Michael D. Setzer iimsetze...@gmail.com To:Michael tiernanmichael.tier...@gmail.com, users@global.libreoffice.org Date sent:Wed, 08 Jul 2020 03:12:21 +1000 Subject:Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet) Priority:normal Did a test with the following: In cell A1 =int(now()) in cell A2 =A1+time(1,12,0) In cell B2 =Datedif($a$1,a2,d)'get days diff in cellC2 =A2-$A$1'gets HH:MM:SS but hours is total in cell D2 = (A2-$A$1)-int(a2-$a$1)'gets the hours minutes seconds in cell E2 =DATEDIF($A$1,A2,d)daysTEXT((A2-$A$1)-INT(A2-$A$1),HhoursMminutes) Result is like 0 days 1 hours 12 minutes in e155 after copying7 days 16 hours 48 minutes Copied A2-E2 to A3 down to E155 The formula in E2 might do what you want?? Did have it in F2 with the additions of seconds, but found the numbers would get off at points with a second. Probable the rounding factor of second portion?? Hope that gives and ideal.Note thespacein E2 formula. Good Luck. On 7 Jul 2020 at 8:34, Michael Tiernan wrote: From:Michael tiernanmichael.tier...@gmail.com Subject:Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet) To:users@global.libreoffice.org Date sent:Tue, 7 Jul 2020 08:34:38 -0400 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 includehoursand minutesI'd like to see it if possible. I have a running log of incidents and it has two sets of columns. First pair isStartandEndof 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 previousEndand the newStartand that I do need to express[# Year][# Months][## Days][## Hours] ## Minuteswhere 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 ash/m/s. Any suggestions? -- MCTMichael C Tiernan. http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet)
Did a test with the following: In cell A1 =int(now()) in cell A2 =A1+time(1,12,0) In cell B2 =Datedif($a$1,a2,d)'get days diff in cellC2 =A2-$A$1'gets HH:MM:SS but hours is total in cell D2 = (A2-$A$1)-int(a2-$a$1)'gets the hours minutes seconds in cell E2 =DATEDIF($A$1,A2,d)daysTEXT((A2-$A$1)-INT(A2-$A$1),HhoursMminutes) Result is like 0 days 1 hours 12 minutes in e155 after copying7 days 16 hours 48 minutes Copied A2-E2 to A3 down to E155 The formula in E2 might do what you want?? Did have it in F2 with the additions of seconds, but found the numbers would get off at points with a second. Probable the rounding factor of second portion?? Hope that gives and ideal.Note thespacein E2 formula. Good Luck. On 7 Jul 2020 at 8:34, Michael Tiernan wrote: From:Michael tiernanmichael.tier...@gmail.com Subject:Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet) To:users@global.libreoffice.org Date sent:Tue, 7 Jul 2020 08:34:38 -0400 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 includehoursand minutesI'd like to see it if possible. I have a running log of incidents and it has two sets of columns. First pair isStartandEndof 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 previousEndand the newStartand that I do need to express[# Year][# Months][## Days][## Hours] ## Minuteswhere 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 ash/m/s. Any suggestions? -- MCTMichael C Tiernan. http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@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 -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Strange question on Macro?? (in Spreadsheet)
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? -- << MCT >> Michael C Tiernan. http://www.linkedin.com/in/mtiernan Non Impediti Ratione Cogatationis Women and cats will do as they please, and men and dogs should relax and get used to the idea. -Robert A. Heinlein -- To unsubscribe e-mail to: users+unsubscr...@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