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!

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!

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.
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!

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!

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.
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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 52 members 4,733 guests
Please welcome our newest community members: