cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Oskarkuus
Level: Powered On

Get one month span? Formula, minus one day in month formula

Hey

 

I am using this formula:

startOfMonth(getPastTime(1,'MONTH'),'yyyy-MM-dd')

to print the date of the first day in the month before the current one.

so to run this in october i would get october 1th

 

then i use this formula:

startOfMonth(utcNow(),'yyyy-MM-dd')

to print the first day in the current month.

 

now i want to print the first day in the current month minus 1 day (-1 day)

 

how can i do this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

If you want to get all events last month, please refer to this method:

58.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Using starts with to compare Date  can avoid some problems.

 

In my experience, it is difficult to define whether the end of the day is 00:00:00 or 23:59:59, so I generally do not include time.

 

Or you could use 2019-09-01/2019-10-01 to filter events, I think the result is the same as starts with.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

13 REPLIES 13
Dual Super User
Dual Super User

Re: Get one month span? Formula, minus one day in month formula

@Oskarkuus 

 

adddays(startOfMonth(utcNow(),'yyyy-MM-dd'),-1)

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Please try to use this expression:

addDays(startOfMonth(utcNow()),-1,'yyyy-MM-dd')

Besides, you could get more details about WDL function from this online doc:

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oskarkuus
Level: Powered On

Re: Get one month span? Formula, minus one day in month formula

Thank you.

 

I have now another problem.

If i chose to do the -1 on adddays function.

I see that i get the timestamp for example like this: 2019-09-30T00:00:00.0000000Z

 

Date is correct, i get the day behind the first of the next month.

But time. Does 00:00:00 mean the START of that date or the end of that date?

 

Goal is to get all events during the last day of the month.

 

So it might be better to use the first filter i had, where i get this timestamp: 2019-10-01T00:00:00.0000000Z

but only if 00.00.00 time means the first thing on that date. then i am sure ill get everything during the timestamp 2019-09-30

 

hope im clear enough

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Have you tried to use the expression I provided?

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oskarkuus
Level: Powered On

Re: Get one month span? Formula, minus one day in month formula

yes works fine

 

but i need to know how the timestamp works.

 

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Instead of discussing your final requirements directly, please let us know your final requirements and your current Flow configuration.

 

Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

If you want to get all the events during the last day of the month.

Please refer to the following method:

56.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oskarkuus
Level: Powered On

Re: Get one month span? Formula, minus one day in month formula

but using that formula i get the timestamp for example: 2019-09-30T00:00:00Z

And the time is bothering me. Because 00,00,00 means the Start of the day, first second first minute of september 30th.

If i would want all events from the last day of the month i would need to have the timestamp less than : 2019-09-30T23:59:59Z

 

Goal is to get all events from the first second on the first day of the month to the last second of that month.

 

GREATER THAN: 2019-09-01T00:00:00Z

startOfMonth(getPastTime(1,'MONTH'),'yyyy-MM-dd')

this works great, i get the first day on the past month and timestamp is the first second of that day so i get everything from the first day and onwards.

 

 

LESS THAN: 2019-09-30T23:59:59Z

addDays(startOfMonth(utcNow()),-1,'yyyy-MM-dd')

above formula would however not produce the time 23.59.59.

above formula would produce 00.00.00 and that means records from that day would not be part of my selection.

 

then maybe removing the -1 and get the timestamp: 2019-10-01T00.00.00Z is better

since it would mean i get items before the start of october 1.

 

 

 

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

If you want to get all events last month, please refer to this method:

58.PNG

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Oskarkuus
Level: Powered On

Re: Get one month span? Formula, minus one day in month formula

how come you just copy paste the last post?

 

the question still applies that i have about the timestamp since the timestamp do appear when i run your formula and the timestamp indicates that i do not get values from the last day since the timestamp is set to the START of the last day, not the END of the last day.

 

00:00:00 indicates to me that it is in the beginning of that day, not just before that day ends.

the scope should be day 1-31

if i have the timestamp day 31 at 00:00:00 i will not get that day since i use "less than" formula in my filter.

so i need the timestamp to be less than 23.59.59

 

if im wrong, explain why the timestamp does not matter.

not just posting the same thing without any explaination.

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Please take a closer look at the expression in the screenshot, they are not the same.

 One is yyyy-MM-dd, other one is yyyy-MM.

Since you need the event for the entire month of September, you only need to check if the event start time starts with 2019-09, so I used starts with.

In addition, September only has 30 days.

Hope it makes sense.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Oskarkuus
Level: Powered On

Re: Get one month span? Formula, minus one day in month formula

yes that makes 100% more sense now!

my mistake for not noticing the missing -dd

 

but now im very curious to understanding the differense

would i get the same result if filtering on "2019-09 (startswith)"

or filtering by "greater than 2019-01-01T00:00:00Z and less than 2019-10-01T00:00:00Z"

 

would the end result be the same, including all days in september at all times?

Community Support Team
Community Support Team

Re: Get one month span? Formula, minus one day in month formula

 

Hi @Oskarkuus ,

 

Using starts with to compare Date  can avoid some problems.

 

In my experience, it is difficult to define whether the end of the day is 00:00:00 or 23:59:59, so I generally do not include time.

 

Or you could use 2019-09-01/2019-10-01 to filter events, I think the result is the same as starts with.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors (Last 30 Days)
Users online (4,964)