web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : 76wdEFr65gvj192QLPRUN2
Power Automate - General Discussion
Unanswered

How to calculate the week beginning from a a Start Date from excel

Like (0) ShareShare
ReportReport
Posted on 26 Mar 2023 21:41:24 by 9

Hi all, Is it possible to calculate the week beginning from a Start Time received from an excel column using power automate?

 

I have a number of users that complete a form every week this is stored in an Excel sheet, I have a 'Start time' Column in this sheet and am looking to be able to split the comments by week beginning and add / update in a SharePoint list.

 

I have originally got the flow to work by completing this formula in excel using the following formula=[@[Start time]]-MOD([@[Start time]]-5,7).

 

However my flow fails every time with the last input on the 'Apply to each' with an invalid date of -2. is this because the spreadsheet needs to be opened to update or should this be done on the flow?

"item/Response_x0020_ID":"98","item/WeekBeginning":"1899-12-28","item/Comments":"Invalid date","item/Score":"6"}}

 

 

  • AntonJambon Profile Picture
    9 on 28 Mar 2023 at 09:23:30
    Re: How to calculate the week beginning from a a Start Date from excel

    (309) Effortlessly Format Dates & Get Specific Days: Power Automate Solution - YouTube

  • SudeepGhatakNZ Profile Picture
    14,381 Most Valuable Professional on 27 Mar 2023 at 20:22:26
    Re: How to calculate the week beginning from a a Start Date from excel

    Thats great! If you please link the article here, others might benefit from it.

     

  • AntonJambon Profile Picture
    9 on 27 Mar 2023 at 11:32:12
    Re: How to calculate the week beginning from a a Start Date from excel

    I've got this resolved, I found a video on Youtube by DamoBird365 which fully explained the expressions and filter arrays.

  • AntonJambon Profile Picture
    9 on 27 Mar 2023 at 09:10:34
    Re: How to calculate the week beginning from a a Start Date from excel

    Hi @SudeepGhatakNZ,

     

    Apologies but i'm new to Power automate and don't quite fully understand the processes, below is the formula i have been using, i have calculated the week number now but can't calculate a week beginning from it it states the expression is invalid I have tested both the addDays and the dayOfWeek expressions individually and they work its just when i try to pass the start time into it, it just falls over

     

    addDays('2023-01-01', (int('variables('WeekNumber')')) -1) * 7 - dayOfWeek('2023-01-01'))
     
    I have also tried this from to get the date directly from the Start time column
     
    formatDateTime(addDays(startOfDay(convertTimeZone(triggerBody()?['Start time'], 'UTC''GMT')), 1 - dayOfWeek('2023-01-01''Sunday')), 'MM/dd/yyyy')
  • SudeepGhatakNZ Profile Picture
    14,381 Most Valuable Professional on 27 Mar 2023 at 00:55:59
    Re: How to calculate the week beginning from a a Start Date from excel

    @AntonJambon ,

    You should use date expressions in Power Automate instead of adding a formula in excel.

    You can achieve the same expression in Power Automate.

    SudeepGhatakNZ_0-1679878550286.png

     

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2

Loading complete