Last 7 workday of every month

[desmond_lee]

Hi All

T create 7 days in future after a define date is easy (IF(OR([CCNX]=0,[CSGK]=0,[CTWN]=0,[CCNZX2]=0,[CCNZX1]=0,[CCNKB]=0,[CCNZB]=0,[CMYP]=0),WORKDAY(DATE([SHARING DATE]), 7),"")).

Is is possible to create last 7 workday of every month?

[TeeSee1 · Accepted solution]

@Aurelien 

I completely missed that function! 

Can we do then WORKDAY(EOMONTH(TODAY()), -6) instead?

[TeeSee1]

I would try to go backward from the first day of everymonth

[desmond_lee]

So how to do that? Show some expression examples.

[TeeSee1]

WORKDAY("2024/05/01", -7)

You can construct the first day of the following month of a given date by using MONTH(),YEAR() and CONCATENATE() and IF(). IF is needed because in December you need to change the year as well as cycle back to Jan.

[desmond_lee]

That working forward. I find ot difficult to do it forward because some month is 30 days, 31 days or 28 days.

[Aurelien]

What about using this?

EOMONTH(TODAY(), 0) + 1 returns first day of next month

For reference:

EOMONTH() - AppSheet Help

[TeeSee1 · Accepted solution]

@Aurelien 

I completely missed that function! 

Can we do then WORKDAY(EOMONTH(TODAY()), -6) instead?

[desmond_lee · Accepted solution]

TeeSee

Thank so much. So error in your expression and I manage to correct it. This is the correct expression WORKDAY(EOMONTH(TODAY(), 0), -6)