Calculate the duration of an event.

[Anonymous]

I am trying to create an app which will store data into an SSMS table and I am currently getting hung up on the duration of something. So in the form the users will fill out there is a start time and an end time field and I want to be able to calculate the duration of the event however I am not sure what the best way to go about this is. Right now I have a sql table with all the columns I need, in particular the column which will hold the start times is a datetime format and the column that holds the end times is also a date time format however the duration is simply a time column type.  Any ideas? Am I even using the correct sql column time for the duration or should that also be date time? Thanks in advance

[seadude]

Hi @Anonymous ,

PowerApps has MANY ways to manipulate date and time formats, calculate durations, etc. With all of that power, comes some decent levels of complexity (and some just WEIRD syntaxes 🙂 ).

My suggestion is for you to look up the following Functions in the PowerApps reference docs:

• Date()
• Time()
• DateDiff()
• DateAdd()
• DateTimeValue()
• TimezoneOffset()
• Text ()

Also searching for "duration" in the forums here yeilded 150+ results. I absolutely know this question has been answered before WITH great sample apps in some cases.
Please have a look around then let us know how you fared. The method you use to FIND your answers will be valuable to others.
Good luck!

[jdobkows]

For example you can calculate in minutes

Set(_startDate, Now());

....other operations

Set(_endDate, Now());

Set(_duration, DateDiff(_startDate, _endDate, Minutes))

[seadude]

This is also one of my favorite references for duration differences:

- @mr-dang 's Countdown Timer video.

[v-yutliu-msft · Accepted solution]

Hi @Anonymous ,

Do you want to update the duration column according to the data in ‘Start Time’ and ‘End Time’?

Could you show me your formula or the error?

I’ve made a similar test as follows, you can refer this:

Firstly, A Primary Key is required for the following operations: GetItem, UpdateItem, DeleteItem.

Secondly, the data type of time is used to record time not duration. The data type of Time has a limit that only allows value between 00:00:00 and 23:59:59.

So, I recommend you change the ‘Duration’ column’s data type to  varchar(50).

You can try this formula:

RoundDown(
                       DateDiff('Start Time_DataCard1'.Update,
                                      'End Time_DataCard1'.Update,Minutes)/60,0)
&":"
&
DateDiff('Start Time_DataCard1'.Update,
'End Time_DataCard1'.Update,Minutes)-
RoundDown(
                         DateDiff('Start Time_DataCard1'.Update,
                                       'End Time_DataCard1'.Update,Minutes)/60,0)*60

DateDiff can only return the values between the start day and end day, not in the format of Time type.

Here’s the  doc that you can refer:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-dateadd-datediff

Best regards,

Community Support Team _ Phoebe Liu

[Anonymous]

Hello, that is correct I am trying to find the duration of a certain event based on the 'start time' and 'end time' values on my form. The formula I am currently using is as follows:

Concatenate(Text(Value(HourValue6.Selected.Value)-Value(HourValue5.Selected.Value)),":",Text(Value(MinuteValue6.Selected.Value)-Value(MinuteValue5.Selected.Value)))

However this is definitely not correct because it doesn't take into account the fact that time cannot be negative, I've attached a screenshot to show an example of when my logic fails, here the duration should be 30 mins. 

Secondly your recommendation then is for me to make the column varchar and worry about the formatting for the duration in the applciation before it is patched to the datasource? Thanks for the help! I appreciate all the resources as I am new to time manipulation.

[ericonline]

Hi @Anonymous ,
I (almost) guaruntee that what you need can be found in this post:

https://powerusers.microsoft.com/t5/General-Discussion/Need-help-with-diffing-times-commented-code/m-p/134813?advanced=false&collapse_discussion=true&filter=location&location=forum-board:PowerAppsForum1&q=commented%20code&search_type=thread

[Anonymous]

Sorry for the slow reply, I have been working steadily on this project and did not have a chance to get back to you, this formula ended up being exactly what I needed thanks a bunch! If you had a second I did end up having a follow up question, I am now patching my data to the datasource to create the new record based on the information entered by the user however, when I patch the start time and end time seems to be offset when it is saved to the datasource, do you know why that could be? for example if I patch the datacard with the time 8:00 selected for the start time and then go and check the data source the source is showing the record with a time of 12:00, the same issue arises with the end time being offset. The duration however is exactly what I needed so thanks again for that!

[ericonline]

Hi @Anonymous ,
You might check the region settings if your datasource is Sharepoint. Also, unless you do a conversion, times will be displayed in users local time. If the datasource is in a different timezone, your problem arises. 

Check out TimezoneOffset() function. Its not the easiest to understand but run some experiements. Maybe you use it to add/subtract hours to match your datasource.

Good luck!

[Anonymous]

Yeah, I am doing some research and I think it is related to my source. Seems like SQL Server Management Studio automatically converts the times to UTC. I can always adjust it on the application side to counteract the calculation that SSMS will do. Thanks for all the help!