Showing results for 
Search instead for 
Did you mean: 
Community Champion
Community Champion

NOT FOR THE FAINT OF HEART: (RE)Calculate Time Durations... on the fly?

Hello PowerApps community,


I'm stuck here on something pretty complex. I hope I can explain it clearly here and we can come up with some ideas for how to tackle. 



- Users select an Event from dropdown

- A timestamp for Now() is generated

- User can adjust timestamp to actual for the activity and add comments as needed

- I also have some hidden fields that calculate the duration between the LAST submitted record and the CURRENT one

- User clicks "Add Event" button

- OnSelect of the "Add Event" button, Event, Timestamp and Duration are collected and displayed in a Gallery below

- This is all good as long as they record Events IN ORDER




The Problem:

- The list of items in the Events dropdown is ~10-12

- If user adjusts a new Event Timestamp to be a time BEFORE the last Timestamp in the Collection, the Duration is off

  - Again, this is because the hidden helper fields calculate the Duration based on the LAST Timestamp in the Collection and the current Timestamp


Example of the issue:





- How would I lookup in the collection, where the current Event Timestamp should "fit", then calculate Duration?


Skilled Sharer
Skilled Sharer

I'm not sure I 100% understand, but I'll take a stab; what if you don't generate the Now() when the user selects the event, and then just make them enter the datetime? If they have the option to backdate something, just remove the convenience of having it auto-populate Now().

Community Champion
Community Champion

Thats a good thought. I thought about disallowing them to backdate.

To accommodate:

- They'd have to delete all Events in the Gallery until they reach a Timestamp less than the one they are trying to enter

- Then recreate all the deleted events afterwards

Pretty hard on them for making a simple mistake (missing an Event, wanting to be more accurate, etc.).
I'm trying to find a simple way to "Insert" the Timestamp with correct Duration calculation.

Skilled Sharer
Skilled Sharer

What if instead of calculating duration based on the last submitted record and the current one, you calculate it off a fixed datetime? 

Community Champion
Community Champion

Yeah, thats closer.


Instead of a "fixed date", I need to measure from the Timestamp in the collection that is less than the one being added currently.


The challenge is, the Duration of the record after the new Timestamp needs to be recalculated. Its now based of a new value instead of its original.


For Example:

- In my second screenshot above,

- The "Fourth Event" entered becomes the "First Event" in the timeline

- The previous "First Event" becomes the "Second Event" in the timeline

- The now "Second Event"'s Duration must be recalculated from "-" to "60:00"

- The now "First Event" Duration is the new baseline of "-"

- If a new Timestamp is inserted between two existing Events (rather than the earliest in the timeline), two Durations need to be calculated.

Hope that is clear, its challenging to explain. 🙂

Helpful resources

PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group


Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (57,100)