Finding the most recent workday using Basic

English
#1

I am trying to include in a macro a variable that contains the most recent workday date, which could be the present date. I have tried using WORKDAY() and WORKDAY.INTL() to try and find the most recent workday. The problem I am trying to solve is if I am on a weekend day, I need to know the most recent work day, which would be the previous Friday. I have tried various forms of

=WORKDAY(TODAY();-1)
=WORKDAY(WORKDAY(date;1);-1) 
=WORKDAY.INTL(TODAY();-1)

and I get errors on unmatched parentheses, among other things. Can someone suggest the correct syntax for solving this issue?

#2

Slightly OffTopic the Calc formulas for those who don’t want to create a macro for the purpose:
=TODAY()-MAX(WEEKDAY(TODAY();2)-5;0) or
=TODAY()-(WEEKDAY(TODAY();2)-5)*(CURRENT()>0) or
=TODAY()-CHOOSE(WEEKDAY(TODAY();2);0;0;0;0;0;1;2)
…
If the date to apply the formula to is given by cell references instead of TODAY() you cannot use the first formula with array-evaluation because MAX() converts its parameters to a NumberSequence even if equally dimensioned arrays are given.

Macro code

Function lastWorkday(pDate As Long) As Long
Dim h As Long
h = WeekDay(pDate, 2) - 5
lastWorkday = pDate - IIf(h<1, 0, h)
End Function

The second parameter of the Basic function WeekDay seems not to be documented. It may not work this way in older versions.

===EDIT 2020-03-16 about 00:00 UTC===
And the second variant avoiding the usage of the scond parameter which probably not is supported by very old versions:

Function lastWorkday_V(pDate As Long) As Long
Dim h As Long
h = WeekDay(pDate-1) - 5
lastWorkday_V= pDate - IIf(h<1, 0, h)
End Function

Seee this attached example.

#3

When I run this I get: BASIC syntax error. Parentheses do not match.

And I have gotten this before trying various forms of WEEKDAY(). Are any declarations required to use the WEEKDAY() function in LibreOffice??

#4

If I run this statement in a cell it runs fine but it will not run when used in a macro in an assignment statement, e.g.: xx = TODAY()-MAX(WEEKDAY(TODAY();2)-5;0)

#5

The formulas given by me are Calc formulas.
You didn’t tell you wanted to use the expression in Basic. Thats very different insofar, THE MAX() function doesn’t exist in Basic, the parameter separator is different, …
Please expllain what you actually need.

#6

Programming macros in Basic you need to know a bit about Basic.

#7

I do know a bit about Basic, but a bit. I have about a 300 line macro I need to insert this into and actually at the top of my post in the first line I said, “I am trying to include in a macro a variable …”. I need a Basic-compatible statement that assigns to a declared variable the date of the most recent workday (actually stock market day, but … work day will do). I have scoured the LibreOffice and OpenOffice documentation and nothing I have tried so far has worked.

WORKDAY seems to be the function to use but no matter what I do, it throws an error.

#8

I must have missed. Add a tag macro next time to avoid misunderstandings.

#9

The function as written gives me the next workday, and not the previous one. I will try to dig into this and see if I can debug it. It appears that WeedDay(a, b) is not returning the correct value for Sunday (today). It acts as if b = 1 instead of 2, and it is returning a value of 1 for Sunday. That messes up the calculation??

#10

???
Just revisited and even tested. The function worked as expected.
See attachment to my answer made by editing.

#11

OK. Chasing ghosts here. I got a different type number working that involves a different subtracted constant but your equations are correct, too They give the same result. Thanks! Also, you could use type = 7 and just subtract h without adjustment using the same iif statement.

#12

The second parameter of the Basic function WeekDay seems not to be documented

tdf#131368

It may not work this way in older versions

It was implemented in 2004 and worked exactly the same way (just FYI, to allow confident use).

@EMS1 wrote:

Also, you could use type = 7 and just subtract h without adjustment using the same iif statement

This is wrong. You cannot “just subtract h without adjusting” (whatever that ambiguous phrase could mean), even not taking bad logic of such use (using fake “first day of week is Saturday”) into account. You would need a different iif statement (like IIf(h>2, 0, h)) if your phrase still means you intend to use it…

#13

@mikekaganski: Thanks for reporting the bug (lacking docu).

#14

I don’t see a reason to add that “… the scond parameter which probably not is supported by very old versions” clarification, when the optional second parameter was introduced with the function itself in 2004, as said in the bug report!

#15

@Mike2 - yes, that’s what I meant. Whereas only 3 Types appear in the table for Weekday() in the documentation (and one entry is wrong In the documentation), one can use Types 1 through 7. All that does is shift the integer returned by Weekday() for a given day. Yes, one still needs the if statement else it won’t gone the correct answer.

#16

Whereas only 3 Types appear in the table for Weekday() in the documentation

Which documentation do you refer to? You seem to:

  1. refer to a really outdated documentation for WEEKDAY spreadsheet function, that still had all three types described correctly (which has normal up-to-date version, listing 10(!) types);
  2. still not realize that spreadsheet function has nothing to do with BASIC function (which has different number and meaning of types, and which indeed misses any description of the optional argument - you might find relevant VBA documentation link in the bug I filed).
  3. and of course - you incorrectly use Answer (a thing for solutions for the original question) for what is comment.

See also: tdf#131382.

#17

May I humbly ask if the Original Questioner might eventually declare whether the functions I proposed are working correctly or not?
I still think both of them do.
(I personally preferred the first one originally because ISO 8601 specifies the monday to be weekday number 1, and the one-parameter-only calls to the Weekday() functions of LibO Basic and Calc as well assign number 1 to the sunday - which is a really outdated option.)

#18

If you want to use a Calc function inside a BASIC program you must use an LibreOffice API call.

Example: Calling Calc functions in Basic:

Function MyVlook(item, InRange As Object, FromCol As Integer)
    Dim oService As Object
    oService = createUnoService("com.sun.star.sheet.FunctionAccess")
    REM Always use the function English name
    MyVlook = oService.callFunction("VLOOKUP",Array(item, InRange, FromCol, True))
End Function

https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/03131600.html?DbPAR=BASIC#bm_id731561653332192

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.