Unanswered

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

Like (0) Share

Report

Posted on 26 Mar 2023 21:41:24 by AntonJambon

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"}}

I have the same question (0)

All responses (5)

Answers (0)

SudeepGhatakNZ 14,381 Most Valuable Professional on 27 Mar 2023 at 00:55:59

Like (1)

Report

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.

Was this reply helpful? Yes No
AntonJambon 9 on 27 Mar 2023 at 09:10:34

Like (0)

Report

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')

Was this reply helpful? Yes No
AntonJambon 9 on 27 Mar 2023 at 11:32:12

Like (1)

Report

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.

Was this reply helpful? Yes No
SudeepGhatakNZ 14,381 Most Valuable Professional on 27 Mar 2023 at 20:22:26

Like (0)

Report

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.

Was this reply helpful? Yes No
AntonJambon 9 on 28 Mar 2023 at 09:23:30

Like (0)

Report

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

Was this reply helpful? Yes No