Had a sheet that has two dates and calculate the difference between the dates.
07/05/2020 04/11/1960 60 Years 2 Months 24 Days The formula being: =IF(DATEDIF(A2,A1,y),IF(DATEDIF(A2,A1,y)=1,1 Year,DATEDIF(A2,A1,y)Years),)IF(MOD(DATEDIF(A2,A1,m),12),IF(MOD(DATEDIF(A2,A1,m),12)=1,1 Month,MOD(DATEDIF(A2,A1,m),12)Months),)IF(DATEDIF(A2,A1,md),IF(DATEDIF(A2,A1,md)=1,1 Day,DATEDIF(A2,A1,md)Days),) That excludes years months and days if value of unit is 0, and also makes the context singular if value is 1. Short version that just puts the values is: =DATEDIF(A2,A1,y)YearsMOD(DATEDIF(A2,A1,m),12)MonthsDATEDIF(A2,A1,md)Days Originally wanted to just copy the formula, and change the A2,A1 to the new values. Recording it worked just fine, and did everything correctly in the recording process, but the play back didn't include the last steps using the F2 key. I've just now done a new version that does seem to work, but it made the process a lot longer... Was a mess getting all thes correct, but it seems to work the way I want, just required manually replacing each value versus using the search and replace substitute command?? sub Z3 rem ---------------------------------------------------------------------- rem define variables dim documentas object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document= ThisComponent.CurrentController.Frame dispatcher = createUnoService(com.sun.star.frame.DispatchHelper) rem ---------------------------------------------------------------------- dim sText sText = InputBox (Please enter Date Cells Example D2,D1:) rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name =StringName args1(0).Value ==IF(DATEDIF(sText,y),IF(DATEDIF(sText,y)=1,1 Year,DATEDIF(sText,y)Years),)IF(MOD(DATEDIF(sText,m),12),IF(MOD(DATEDIF(sText,m),12)=1,1 Month,MOD(DATEDIF(sText,m),12)Months),)IF(DATEDIF(sText,md),IF(DATEDIF(sText,md)=1,1 Day,DATEDIF(sText,md)Days),) dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1()) end sub On 4 Jul 2020 at 20:54, Andrew Pitonyak wrote: From:Andrew pitonyakand...@pitonyak.org Date sent:Sat, 04 Jul 2020 20:54:34 -0400 Copies to:users@global.libreoffice.org To:Michael D. Setzer iimsetze...@gmail.com Subject:Re: [libreoffice-users] Confused with Macro results?? Might I ask what you want the macro to do? Your macro confuses me a bit... That said, the actual API has a very harsh learning curve (in my opinion). If I rip apart what I see, I would guess the following: The user enters some text, which represents a set of cells separated by a comma. Next, you do things with these cells based on a very complicated formula. I am not familiar with the EnterString dispatch off hand, but I assume that it places whatever this string is into a cell. Next you call the dispatcher to Copy to the clipboard followed by Cutting to the clipboard. That seems redundant. Next you have InsertContents, again, a dispatch command with which I have no familiarity, but, off hand it looks like it is probably doing something similar toPaste, but I am just guessing. So, what do you really want to accomplish? On Saturday, July 04, 2020 18:30 EDT,Michael D. Setzer IImsetzerii@gmail.comwrote: Been doing a lot of googling, but finding lots of stuff, but nothing that works? Have done a lot of versions and this is the latest one. The record process does not allow for the use of the F2 key, so as is, it results in the original args1(0).Value being in the cell? After the macro ends, I've found that if I manually press F2 then a space and enter it puts the formula as I originally wanted? F2 and enter does nothing?? I've also found that if I manually do F2 F9 Enter Enter, it will place just the final text result in cell. Seems there use to be a Keypress option, that would allow for simulating keys, but that has been deprecated? Also, found pages that talk about API stuff to modify cells, but the two I tried did nothing? Am I missing something?? Note: In string I replaced the originals with _ because it kept giving me messages about unbalanced ()?? That got rid of the errors, and then just converted them back? Not and important macro, but was just hoping to find a way to make it work the way I originally planned. Just playing around with stuff. Thanks. Perhaps I am overlooking something very simple.. sub Z1 rem ---------------------------------------------------------------------- rem define variables dim document as object dim dispatcher as object rem ---------------------------------------------------------------------- rem get access to the document document = ThisComponent.CurrentController.Frame dispatcher = createUnoService(com.sun.star.frame.DispatchHelper) rem ---------------------------------------------------------------------- dim sText sText = InputBox (Please enter Date Cells Example D2,D1:) rem ---------------------------------------------------------------------- dim args1(0) as new com.sun.star.beans.PropertyValue args1(0).Name =StringName args1(0).Value = =SUBSTITUTE(SUBSTITUTE(=IF(DATEDIF(A2,A1,_y_),IF(DA TEDIF(A2, A1,_y_)=1,_1 Year _,DATEDIF(A2,A1,_y_)_ Years _),__)IF(MOD(DATEDIF(A2,A1,_m_),12),IF(MOD(DATEDIF(A2, A1,_m_),1 2)=1,_1 Month _,MOD(DATEDIF(A2,A1,_m_),12)_ Months _),__)IF(DATEDIF(A2,A1,_md_),IF(DATEDIF(A2,A1,_md_)=1,_ 1 Day _,DATEDIF(A2,A1,_md_)_ Days_),__),A2,A1,chr$(34) sText chr$(34)),_,CHAR(34)) dispatcher.executeDispatch(document,.uno:EnterString,, 0, args1()) rem ---------------------------------------------------------------------- dispatcher.executeDispatch(document,.uno:Copy,, 0, Array()) rem ---------------------------------------------------------------------- REM Cut contents of cell to avoid the overwrite message dispatcher.executeDispatch(document,.uno:Cut,, 0, Array()) rem ---------------------------------------------------------------------- dim args2(5) as new com.sun.star.beans.PropertyValue args2(0).Name =Flags args2(0).Value =SVD args2(1).Name =FormulaCommand args2(1).Value = 0 args2(2).Name =SkipEmptyCells args2(2).Value = false args2(3).Name =Transpose args2(3).Value = false args2(4).Name =AsLink args2(4).Value = false args2(5).Name =MoveMode args2(5).Value = 4 dispatcher.executeDispatch(document,.uno:InsertContents,, 0, args2()) msgbox (To complete process,+chr$(13)+Formula Press F2 then Space then Enter+ chr$(13)+For Text Result Press F2 then F9 then Enter then Enter) end sub +------------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscrib e/ 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