cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dngtie
Level: Powered On

Calculate the duration of an event.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Calculate the duration of an event.

Hi @dngtie ,

 

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

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

9 REPLIES 9
Highlighted
seadude
Level 10

Re: Calculate the duration of an event.

Hi @dngtie ,

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
Level: Powered On

Re: Calculate the duration of an event.

For example you can calculate in minutes

Set(_startDate, Now());

....other operations

Set(_endDate, Now());

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

seadude
Level 10

Re: Calculate the duration of an event.

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

- @mr-dang 's Countdown Timer video.

Community Support Team
Community Support Team

Re: Calculate the duration of an event.

Hi @dngtie ,

 

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

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

dngtie
Level: Powered On

Re: Calculate the duration of an event.

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.

Super User
Super User

Re: Calculate the duration of an event.

Hi @dngtie ,
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...

dngtie
Level: Powered On

Re: Calculate the duration of an event.

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!

Super User
Super User

Re: Calculate the duration of an event.

Hi @dngtie ,
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!

dngtie
Level: Powered On

Re: Calculate the duration of an event.

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!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 346 members 6,868 guests
Please welcome our newest community members: