Unanswered

Flow works but retrieving zeroes for calculated column in excel (cloud)

(0) Share

Report

Posted on by rhaddad87

Hi, everyone! I have a flow that works fine, it gets the latest response from a form and emails the submitter of the form a summary of their responses. The flow works and gets the excel row information as intended, see below:

My problem is I have a column (Flight Hours) that calculates flight hours in the excel spreadsheet based off the column values, and that result is always 0 in the email. I thought maybe adding in a 1 minute delay would help but it does not. Am I missing something here? Why do calculated columns pose issues? I imagine it's the same reason why the date/time is returning an integer and not a formatted date value.

Thank you for your help

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

PowerAppsWizard 388 on at

Like (0)

Report

Are you calculating the flight hours based on the response details?
If so, wouldn't it be easier to calculate it in the flow itself pass it to the email report?

Was this reply helpful? Yes No
CU-18081211-6 9,270 Moderator on at

Like (0)

Report

@rhaddad87 ,

Can you show a more detailed view of the Excel - Get row action and Send an email notification - action. I'm interested in the formula you used in email for flight hours.

Was this reply helpful? Yes No
rhaddad87 24 on at

Like (0)

Report
@PowerAppsWizard @gabibalaban Thank you for your response! Sorry for my late reply.

Correct, the solution is to calculate the flight hours in the flow itself. I've tried for 3 days to figure it out but keep getting caught up in the code with the converting and calculating, perhaps you can help? Here are the details:

The excel sheet that collects the Form responses stores date and time in separate columns for both take off and landing dates and times (4 columns total)
Date is stored as excel 'Date' type, Power Automate returns the value for 1/15/2022 as 44576.
Time is stored as a string type, Power Automate returns a string of 0900 for a time that represents 9am
Power Automate needs to pull in those two values for each take off date and landing date, store them as a DateTime variable so that PowerAutomate can calculate the amount of hours that passed between the two values. After that, I need to return a float for the number of flight hours between the dates and times.
This is all done in my excel sheet using the following calculations to convert hours to decimal flight hours after subtracting take off datetime from landing datetime, =IFERROR(MOD(IF(AD441=1," ",[@Check]*24),24),0)
If the total flight hours are say, 15 and a half hours, the total flight hours should report 15.5, not 15:30
AD441 is just a cell to check for errors and return a zero if there is one, formula here: =IFERROR(AC441-AB441,0)
After it performs all these calculations, I believe the PowerAutomate flow will be complete.

I hope I have provided enough information to help. I would like to learn this stuff myself, do you suggest any learning courses or programs to become proficient in Power Automate?

As always... THANK YOU SO MUCH!! ❤️

Was this reply helpful? Yes No
rhaddad87 24 on at

Like (0)

Report

I can't delete this thread, but moving it to a more appropriate section, building flows, link here: https://powerusers.microsoft.com/t5/Building-Flows/Convert-4-strings-to-2-dates-with-times-and-calculate-time/m-p/1422449#M159830

Was this reply helpful? Yes No
AnnaWu0901 3 on at

Like (0)

Report

I also face same problem....retrieving zero only for calculated column (based on response)...
As my user need the Flow urgently, I help her to do calculation in Flow....However, as she keeps to create Forms, if I calculate in Flow, I need to provide customized Flow for all Forms which created by her.....

Was this reply helpful? Yes No