Answered

Adding a "0" in :01 to :09 minutes in datediff

(0) Share

Report

Posted on by swong89

Hello everyone.

I followed @ShaneYoung 's videos on datediff for calulating and created a component to allow me to transfer the text version of the datetime to SharePoint. I need the date component since the timekeeping we do includes a night tour that rolls into the next day, and we would need to calculate the total time.

In creating the datediff between time and using the mod to create the hh:mm format, I ran into an issue with the minutes between :00 and :09. Those differences would yield something like this: 12:1 or 2:9 (12 hours and 1 minute, 2 hours and 9 minutes).

Question: How can append a 0 for the minutes?

RoundDown(DateDiff(STDATETIME_START.'24FullDateTime',STDATETIME_END.'24FullDateTime',Minutes)/60,0) & ":" & Mod(DateDiff(STDATETIME_START.'24FullDateTime',STDATETIME_END.'24FullDateTime', Minutes),60)

Categories:

Building Power Apps

All responses (4)

Answers (3)

Sort by

Verified answer

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @swong89 ,

Assuming those are date and time fields, I just tested the below and it worked perfectly across several days

With( { wTime: DateDiff( STDATETIME_START.'24FullDateTime', STDATETIME_END.'24FullDateTime', Minutes ) }, RoundDown(wTime/60,0) & ":" & Mod(wTime,60) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps
swong89 43 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried your cleaned-up formula as well. It still yielded a 1:1 instead of 1:01.

Yes, the fields are datetime variables, and I applied the datatimevalue around it as well to make sure it is datetime.
Verified answer

swong89 43 on at

Like
a
(0)

Report
Copy link

Link copied!
@WarrenBelz I think I found the answer if do a conditional. Let me know if this sounds correct.

With( { wTime: DateDiff( STDATETIME_START.'24FullDateTime', STDATETIME_END.'24FullDateTime', Minutes ) }, RoundDown(wTime/60,0) & ":" & If(Mod(wTime,60)<10,"0" & Mod(wTime,60),Mod(wTime,60)) )
I applied a condition for minutes in diff less than 10 that would append the "0" to the single minutes
Verified answer

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @swong89 ,

I actually forgot about single minutes - you just need this

With( { wTime: DateDiff( STDATETIME_START.'24FullDateTime', STDATETIME_END.'24FullDateTime', Minutes ) }, RoundDown( wTime / 60, 0 ) & ":" & Text( Mod( wTime, 60 ), "00" ) )