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

Calculate time difference with a free text field from SP

Hey there I have two free text fields (from SP) which includes numbers like 09:00 (9am) and 12:15 (12:15pm). I'd like to calculate the hours between those two times. Since it's a free text field including a ":", I think PowerApps doesn't recognize it as a time. Is there a way to do that?

 

I've been trying with DateDiff(StartTime1,EndTime1,Hours) with some variations, but it doesn't seem to work, really.

Cheers

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
seadude
Level 10

Re: Calculate time difference with a free text field from SP

Hi @leemex and @KickingApps,

You can turn text times (such as "09:00") into times using the DateTimeValue() function.

Try: DateDiff(DateTimeValue(Label1.Text),DateTimeValue(Label2.Text)). This will work for simple things like Start and End Times which occur on the same day.

When your events span midnight, complexity abounds! To travel down that rabbit hole, have a look at this post and any others by @CarlosFigueira + "time".

Good luck!

View solution in original post

leemex
Level: Powered On

Re: Calculate time difference with a free text field from SP

Thank you! 

Seems like I was always missing the ".Text" option to get rid of the error.

 

 

At the end of the formula I also had to define the unit. By default the unit is set to "days" which results in a number 0. I needed to change it to "Hours" or "Minutes" as well.

E.g.:

 

DateDiff(DateTimeValue(DataCardValue1.Text),DateTimeValue(DataCardValue2.Text),Hours)

 

 

Anyway, thank you so much!

View solution in original post

Community Support Team
Community Support Team

Re: Calculate time difference with a free text field from SP

Hi @leemex,

Based on the needs that you mentioned, I think the DateTimeValue function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:8.JPG

Set the Text proeprty of the Label control to following:

DateDiff(DateTimeValue(TextInput1.Text),DateTimeValue(TextInput2.Text), Minutes)

On your side, you should type following:

DateDiff(DateTimeValue(StartTime1), DateTimeValue(EndTime1), Minutes)
DateDiff(DateTimeValue(StartTime1), DateTimeValue(EndTime1), Hours)

In addition, I also think the Split function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the Text property of the Label control to following:

DateDiff(
Time(Value(First(Split(TextInput1.Text,":")).Result), Value(Last(Split(TextInput1.Text,":")).Result), 0),
Time(Value(First(Split(TextInput2.Text,":")).Result), Value(Last(Split(TextInput2.Text,":")).Result), 0),
Minutes
)

On your side, you should type:

DateDiff(
Time(Value(First(Split(StartTime1, ":")).Result), Value(Last(Split(StartTime1, ":")).Result), 0),
Time(Value(First(Split(EndTime1, ":")).Result), Value(Last(Split(EndTime1, ":")).Result), 0),
Minutes
)

 

Best regards,

Kris 

Community Support Team _ Kris Dai
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

5 REPLIES 5
KickingApps
Level 8

Re: Calculate time difference with a free text field from SP

@seadude is a wiz with time calcs.  @seadude - have any suggestions on this one?

Highlighted
seadude
Level 10

Re: Calculate time difference with a free text field from SP

Hi @leemex and @KickingApps,

You can turn text times (such as "09:00") into times using the DateTimeValue() function.

Try: DateDiff(DateTimeValue(Label1.Text),DateTimeValue(Label2.Text)). This will work for simple things like Start and End Times which occur on the same day.

When your events span midnight, complexity abounds! To travel down that rabbit hole, have a look at this post and any others by @CarlosFigueira + "time".

Good luck!

View solution in original post

leemex
Level: Powered On

Re: Calculate time difference with a free text field from SP

Thank you! 

Seems like I was always missing the ".Text" option to get rid of the error.

 

 

At the end of the formula I also had to define the unit. By default the unit is set to "days" which results in a number 0. I needed to change it to "Hours" or "Minutes" as well.

E.g.:

 

DateDiff(DateTimeValue(DataCardValue1.Text),DateTimeValue(DataCardValue2.Text),Hours)

 

 

Anyway, thank you so much!

View solution in original post

Community Support Team
Community Support Team

Re: Calculate time difference with a free text field from SP

Hi @leemex,

Based on the needs that you mentioned, I think the DateTimeValue function could achieve your needs.

I have made a test on my side, please take a try with the following workaround:8.JPG

Set the Text proeprty of the Label control to following:

DateDiff(DateTimeValue(TextInput1.Text),DateTimeValue(TextInput2.Text), Minutes)

On your side, you should type following:

DateDiff(DateTimeValue(StartTime1), DateTimeValue(EndTime1), Minutes)
DateDiff(DateTimeValue(StartTime1), DateTimeValue(EndTime1), Hours)

In addition, I also think the Split function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the Text property of the Label control to following:

DateDiff(
Time(Value(First(Split(TextInput1.Text,":")).Result), Value(Last(Split(TextInput1.Text,":")).Result), 0),
Time(Value(First(Split(TextInput2.Text,":")).Result), Value(Last(Split(TextInput2.Text,":")).Result), 0),
Minutes
)

On your side, you should type:

DateDiff(
Time(Value(First(Split(StartTime1, ":")).Result), Value(Last(Split(StartTime1, ":")).Result), 0),
Time(Value(First(Split(EndTime1, ":")).Result), Value(Last(Split(EndTime1, ":")).Result), 0),
Minutes
)

 

Best regards,

Kris 

Community Support Team _ Kris Dai
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

leemex
Level: Powered On

Re: Calculate time difference with a free text field from SP

Thanks! I didn't try the split function, but the .text and defining the units was doing just fine.

 

I'm facing another problem though, I was able to get the hours between those two times (I'm only getting full hours and not minutes yet - e.g. when I'm typing 08:15 and 12:00 it will show 4, instead of 3,75/3:45 - but I think I'm able to figure this out. However, I also have a number, e.g. 1:00 which I need to deduct from the result above.

 

E.g. 4 - 0:45 = 3:15

 

Any idea on how I can do this? The DateDiff is probably not the right choice as it will transform 0:45 to 12:45am.

 

Cheers

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 (Last 30 Days)
Users online (4,584)