cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kouliscon
Helper V
Helper V

formula to check if today is Monday of the last week of the month

hello all,

 

i was wondering is there a formula to check if today is :

 

Monday of the last week of the current month.

 

thank you in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Mira_Ghaly
Dual Super User II
Dual Super User II

@kouliscon 

It is a little bit of Length Solution but will work , please find below steps:

Mira_Ghaly_0-1626733380782.png

Expression for No. 1: This get the first day of the last week of the current  month

int(split(addDays(startOfMonth(body('NextMonth')),-7,'MM-dd-yyyy'),'-')[1])

  

Expression for No. 2: This get the last day of the last week of the current  month

 

int(split(addDays(startOfMonth(body('NextMonth')),-1,'MM-dd-yyyy'),'-')[1])

 

Mira_Ghaly_1-1626733669806.png

Expression for 3: This get the day of week

1= Monday

2=Tuesday 

3= Wednesday ..

Also please note here that you need to convert to your time zone

https://docs.microsoft.com/en-us/previous-versions/windows/embedded/gg154758(v=winembedded.80)?redir...

 

dayOfWeek(convertFromUtc(utcNow(),'AUS Eastern Standard Time'))

 

Expression 4: Day of Month

dayOfMonth(convertFromUtc(utcNow(),'AUS Eastern Standard Time'))

 

Also make sure in the first condition use 1 instead of 2

 

Hope this works for you!

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

6 REPLIES 6
Mira_Ghaly
Dual Super User II
Dual Super User II

@kouliscon 

It is a little bit of Length Solution but will work , please find below steps:

Mira_Ghaly_0-1626733380782.png

Expression for No. 1: This get the first day of the last week of the current  month

int(split(addDays(startOfMonth(body('NextMonth')),-7,'MM-dd-yyyy'),'-')[1])

  

Expression for No. 2: This get the last day of the last week of the current  month

 

int(split(addDays(startOfMonth(body('NextMonth')),-1,'MM-dd-yyyy'),'-')[1])

 

Mira_Ghaly_1-1626733669806.png

Expression for 3: This get the day of week

1= Monday

2=Tuesday 

3= Wednesday ..

Also please note here that you need to convert to your time zone

https://docs.microsoft.com/en-us/previous-versions/windows/embedded/gg154758(v=winembedded.80)?redir...

 

dayOfWeek(convertFromUtc(utcNow(),'AUS Eastern Standard Time'))

 

Expression 4: Day of Month

dayOfMonth(convertFromUtc(utcNow(),'AUS Eastern Standard Time'))

 

Also make sure in the first condition use 1 instead of 2

 

Hope this works for you!

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

eliotcole
Power Participant
Power Participant

Hi, @kouliscon 

 

I think @Mira_Ghaly 's solution is probably better than mine, but I wanted to put all the logic out using pure actions (ish).

 

Once you have this down, sliding much of it into expressions and functions will slim it down considerably, but this plots out the logic easily.

 

I won't explain all of the actions here, because they're all pretty standard, the only tricky one is DO UNTIL which can sometimes be a little funny. If you can't get into the value side of the DO UNTIL, click "Edit in advanced mode" then click back to basic and it will open it up.

 

However if anything looks odd, please say. ... ... Especially if I'm wrong. 😅

 

Explanation

  1. Count up the days until it is next Month.
  2. Then count back the days until you reach a Monday.

 

Full Flow

Full FlowFull Flow

 

Broken Down

VariablesVariables

 

Do Until Next MonthDo Until Next Month

 

Do Until Is Last MondayDo Until Is Last Monday

 

Define MondayDefine Monday

 

Some of the Simple Expressions for Copy/Paste

None of the expressions used here will be alien to a flow user after a few uses, I don't think.

 

timeVAR

This might sound silly to explain, but it's needed because it needs to continually be used and reset. Additionally it gives an easy reference for any expressions

 

nxtMthVAR Expression

 

 

 

int(formatDateTime(body('Add_a_month'), 'MM'))

 

 

 

Firstly this formats the date and time of the "Add a month" action before into to numbers (eg: 07).

Secondly this ensures that when it presents that '07' it is definitely presented as an integer type of number.

 

monthVAR Expression

 

 

 

int(formatDateTime(body('Add_a_day'), 'MM'))

 

 

 

This is nearly the same expression as the nxtMthVAR, so it's basically picking out the numeric representation of whatever month the day addition has brought the flow into.

 

Condition to check if it's next month

This just looks to see if the number that you just generated in monthVAR is the same as the one that you worked out in nxtMthVAR at the very start.

 

If the numbers match, the nxtMthMatchVAR is set to 'true', and the DO UNTIL will not iterate again.

 

dayVAR Expression

 

 

 

 

formatDateTime(variables('timeVAR'), 'dddd')

 

 

 

 

This simply takes the time from timeVAR and displays the day as a word, in full (eg: Monday). 

 

Condition to check if it's Monday

This now checks to see if the day that you just generated in dayVAR is 'Monday'.

 

If it is, then the isMonVAR is set to 'true', and the DO UNTIL will not iterate again.

 

lastMondayOfThisMonthVAR

This can now simply be set to the timeVAR which has now been wound forward, then back, until it is the right day!

@Mira_Ghaly hello, and thank you for the detailed reply.

i was trying to construct the flow based on what you send me but i am having issue with the first step as i dont know where or how to make it. Can you pls advice.

 

Capture.PNG

 

thank you

never mind @Mira_Ghaly  i found it is called "Add to time".. let me see with the rest of the flow which looks great till now...

eliotcole
Power Participant
Power Participant

That was from me, @kouliscon , but it's cool, yeah, sorry about that ... I could've renamed those better. 😅

 

If you just follow the basic premise ... count up to the next month, count back to the last Monday, you'll make it ok. 🙂

 

Everything else will fall in to place after that.

 

Once you have those basics, you'll quickly condense a lot of these steps into expressions like in Mira's answer.

 

@kouliscon 

Let  me know if this has worked for you or you are still facing some challenges!

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,746)