cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kamotekid
Frequent Visitor

Compare date to determine week number of an event

Hi!

Here's a snapshot of the table I'm working on.  I am filtering rows whose start and end dates are current, based on utcNow(), and this flow will run as a weekly report.

kamotekid_0-1624135748331.png

 

In addition, I have a sharepoint list that contains Fiscal calendar details, like WeekOfYear, DayofYear, etc. 

Should I use that to determine what week the 'Title' is in based on the 'Proposed Start Date'?  For example, I want to use flow to output based on today's date, that we are on week '3' of the 'Siig Inc' row because its 'Proposed Start Date' is '07 Jun 2021'. I guess that mean using utcNow() to count number of weeks from 'Proposed Start Date'.

 

The values will be collected into an email body, arranged first by the Select action adn will look like:

'TITLE'  'Proposed Start Date'  'Proposed End Date'  'Event Week Number'

 

I was hoping for an easier way without having to reference the Fiscal calendar SP list, like a formula I can plug into an 'Apply to each' loop.

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
ccc333ab
Solution Sage
Solution Sage

Can you just use a mathematical formula to calcualte it out? Assuming start date is always Monday...you can: 

  • Days difference between Today's Date and Start Date 
  • Then divide that by 7 to see number of weeks (but just the quotient)
  • Then add 1 since the first week will give a zero, but you'll say that is in the first week

So in Power Automate, I'd use this formula: 

add(div(div(sub(ticks(utcnow()),ticks(formatDateTime(variables('strDate'),'yyyy-MM-dd'))),864000000000),7),1)

Just replace variables('strDate') with whatever your start date is. Explaning this a bit but I can explain it moreif you want:  

  • Need to format the date coming out of excel as you'll be getting a string date (thus the formatDateTime expression)
  • the div/sub/ticks/840000000000 stuff is how you go about and calculate out the difference of dates in days
  • div will divide this by 7 (and only returns the quotient)
  • add will add the extra day

View solution in original post

4 REPLIES 4
ccc333ab
Solution Sage
Solution Sage

Can you just use a mathematical formula to calcualte it out? Assuming start date is always Monday...you can: 

  • Days difference between Today's Date and Start Date 
  • Then divide that by 7 to see number of weeks (but just the quotient)
  • Then add 1 since the first week will give a zero, but you'll say that is in the first week

So in Power Automate, I'd use this formula: 

add(div(div(sub(ticks(utcnow()),ticks(formatDateTime(variables('strDate'),'yyyy-MM-dd'))),864000000000),7),1)

Just replace variables('strDate') with whatever your start date is. Explaning this a bit but I can explain it moreif you want:  

  • Need to format the date coming out of excel as you'll be getting a string date (thus the formatDateTime expression)
  • the div/sub/ticks/840000000000 stuff is how you go about and calculate out the difference of dates in days
  • div will divide this by 7 (and only returns the quotient)
  • add will add the extra day
kamotekid
Frequent Visitor

Thanks @ccc333ab 
Ok, I'll try this out today.  If I already have the excel string 'Proposed Start Date' formatted to MM-dd-yyyy from a previous flow, should I still convert it to yyyy-MM-dd first?

 

ccc333ab
Solution Sage
Solution Sage

No, you shouldn't have to just as long as the date is in a date format (not a string). 

kamotekid
Frequent Visitor

@ccc333ab - I just tried it now, and it works flawlessly!

Many thanks!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (3,081)