Calculation to presents the third day prior to the last work day of a month.

[Gilbert13]

Greetings to all, I need help to create a calculation that presents the number of the third day prior to the last work day of a month.
 

Date = 1/5/2022
Month(Date) = 1  "I need the calculation to present  27"

Thanks in advance!!

Edited February 3, 2022 by Gilbert13

[laguna92651]

Day (
Let (
~Date = Date ( 1 ; 5 ; 2022 ) ;
Date ( Month ( ~Date ) + 1 ; 1 ; Year ( ~Date ) ) - 5 ) )

I think this will do it.

Replace the ~Date with the date field you are using to get rid of the Let()
Add 1 month to get the following month.
Subtract 1 day to get the last day of the month in question. Subtract 4 days to go in 3 days from last day of month, (1+4) = 5.
You can adjust the 4 depending on how you're counting days back.

 

[Gilbert13]

Hi, Laguna

The calculation is not presenting the correct number.

Just need the calculation to present the third day prior to the last work day of the month using my date field.

[laguna92651]

Day (
Let (
~Date = Date ( 1 ; 5 ; 2022 ) ;
Date ( Month ( ~Date ) + 1 ; 1 ; Year ( ~Date ) ) - 5 ) )

I think this will do it.

Replace the ~Date with the date field you are using to get rid of the Let()
Add 1 month to get the following month.
Subtract 1 day to get the last day of the month in question. Subtract 4 days to go in 3 days from last day of month, (1+4) = 5.
You can adjust the 4 depending on how you're counting days back.

--------

You said "Work Day", are you saying don't count Saturday and Sunday?

Here is a simplified version.

Day (
Date ( Month ( table::date field ) + 1 ; 1 ; Year ( table::date field ) ) - 5 )

Try this in you Data Viewer with the actual date field.

[Gilbert13]

Yes, Just M-F.

If the value in the date field is: 3/1/2021, the calculation should present: 29

Edited February 3, 2022 by Gilbert13

[comment]

The logic you want to apply here is not clear: 

• The last workday of January 2022 is Monday, jan 31. Three days prior to that is the 28th. If you skip Saturdays and Sundays in the count, then it's the 26th. Yet you say the expected result is 27.
• The last workday of March 2021 is Wednesday, Mar 31. Three days prior to that is the 28th, which is a Sunday. The Friday before that is the 26th. Yet here you say the expected result is 29.

Please explain in more detail what exactly the calculation should do. And also, what about holidays?

 

Edited February 3, 2022 by comment

[LaRetta]

The logic doesn't seem to hold but I *think* you want the 'three days' to be inclusive similar to:

1/5/2022:  last day is 1/31 which is weekday so it counts as does 28th and 27th so you want the 27th.

3/1/2021: last day is 31st which is weekday so it counts as does 30th and 29th so you want the 29th.

Is this what you want?

[comment]

6 hours ago, LaRetta said:

I *think* you want the 'three days' to be inclusive

I would call that "two days before" rather than "three days, inclusive". Otherwise you'll find yourself saying that a date is one day before itself, "inclusive".😀

Now, assuming that is the wanted logic, there is a non-recursive solution - but it cannot account for holidays. OTOH, with only a few steps to take one could do:

While ( [
// start at end of month of given date
start = Date ( Month ( YourDate ) + 1 ; 0 ; Year ( YourDate ) ) ;
n = 2 
] ;
n ;
[
workday = IsEmpty ( FilterValues ( DayOfWeek ( start ) ; "1¶7" ) ) ; 
start = start - 1 ;
n = n - workday
] ;
Day ( start )
)

and this would be to easy to adapt so that it looks at list of holidays and does not advance the counter if the date is listed.

---

ADDED:

Note that this can return a weekend date if, for example, the end of the month is a Tuesday. To always return a workday, you can change the condition to:

n or not IsEmpty ( FilterValues ( DayOfWeek ( start ) ; "1¶7" ) ) ;

When I have more time, I will try and find way to eliminate the double evaluation of the IsEmpty (...) expression.

 

Edited February 3, 2022 by comment

[LaRetta]

CORRECTO - my terminology was incorrecto!!!  Very nice, Michael!!! 🤠

[Gilbert13]

Thank you all very much for responding. I apologize for not making myself understood but as always you are of great help and the calculation works perfectly!!!

It's just what I was trying to achieve.