cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Total Time calculation between value and drop down item

New to all this Powerapps thing and can't get certain expressions to work. Here is my scenario - I have a Date and time picker for TimeFrom and the same for TimeTo using the below expression:

 

DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours) & ":" & Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60)

 

which is working fine to calculate the Total time between the two pickers. I then have a Total Claimed time That needs to look at the value from the above expression and deduct any mealtime selected from the MealTimeDropdownTotal_DataCard1_1 dropdown for a Total Claimed time for the request. Any ideas much appreciated.

10 REPLIES 10
Highlighted
Super User III
Super User III

Re: Total Time calculation between value and drop down item

@Applasaurus 

There's alot to understand  here but I think your code outputs a Text string showing hours and minutes.

DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours) & ":" & Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60)

 

I would suggest converting the calculation to TIME then format via the TEXT function.  Put this code in the text property of a label.

Text(
Time(
DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours),
Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60),
0),
"hh:mm")

 

Then you can more easily subtract meal time.

Text(
Time(
DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours),
Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60),
0)
- Time(meal_hours, meal_minutes, 0),
"hh:mm")

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Helper I
Helper I

Re: Total Time calculation between value and drop down item

Thanks for the help. I've changed the expression to TIME as you specified. I've added the second expression to the TotalClaimedHours card and the error of The function 'Time' has some invalid arguments' I'm guessing this is because that mealtime is a choice dropdown list(This is what the users want instead of a picker). 

Highlighted
Super User III
Super User III

Re: Total Time calculation between value and drop down item

@Applasaurus 

Share your code?

Highlighted
Helper I
Helper I

Re: Total Time calculation between value and drop down item

@mdevaney 

 

Text( Time( DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours), Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60), 0) - Time(DataCardValue8_1, 0), "[$-en-US]hh:mm")

Highlighted
Super User III
Super User III

Re: Total Time calculation between value and drop down item

@Applasaurus 

I am thinking the error is the one highlighted in blue.

 

Text( Time( DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0), Hours), Mod(DateDiff(dtFrom.SelectedDate + Time(Value(HourStart.SelectedText.Value),Value(MinuteStart.SelectedText.Value),0),dtTo.SelectedDate + Time(Value(HourTo.SelectedText.Value),Value(MinuteTo.SelectedText.Value), 0),Minutes),60), 0) - Time(DataCardValue8_1, 0), "[$-en-US]hh:mm")

 

 

The TIME function requires 3 arguments and you have only provided 1.

Time(hour, minute, day)

 

Additionally this reference must be  converted into a value

Value(DataCardValue8_1.Text)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Helper I
Helper I

Re: Total Time calculation between value and drop down item

@mdevaneyis it a mater of just changing the error in blue with the Time to Value(DataCardValue8_1.Text) Or is there more required in the expression to convert the selected item from the mealtime dropdown into a text for the formmula to work?

Highlighted
Super User III
Super User III

Re: Total Time calculation between value and drop down item

@Applasaurus 

Good question.  I would need a screenshot of what the options in the Meal Time dropdown look like to respond 🙂

Highlighted
Helper I
Helper I

Re: Total Time calculation between value and drop down item

@mdevaney  Here is a screenshot of the dropdown: 

 
 

Capture.JPG

 

It's a sharepoint choice list

Highlighted
Super User III
Super User III

Re: Total Time calculation between value and drop down item

@Applasaurus 

I think you would change this code of yours

Time(DataCardValue8_1, 0)

 

Into this code.  From what I know your "Choices" are stored as Text data types.  We must extract the hours & minutes then convert to a number value.

Time(
    Value(Left(DataCardValue8_1.Selected.Value, 2)),
    Value(Right(DataCardValue8_1.Selected.Value, 2)),
    0
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,991)