Build a reusable function that show a user friendly duration based on the number of ticks()

Like (0) Share

Report

Posted on 24 Nov 2024 00:21:58 by johnjohnPter

1,546 Super User 2024 Season 1

I have a Power Automate flow which calculate the duration for each stage (based on the number of ticks() function) during a workflow and populate a sharepoint list accordingly, here is a sample of an item:-

The item has values for the Assigned, UnderReview & InProgress stages, and it also mentioned that the current Status is "For Counter Signature", so this stage still does not have a fixed duration value yet.

Now i want to build a Power Apps gallery which shows the following:-

1) the duration in a user friendly way, by converting the number of ticket() to Days, Hours & Minutes, here is the formula:-

// Calculate total days, hours, and minutes
Set(varDays, RoundDown(varNoTicks / 864000000000, 0));
Set(varRemainingTicksAfterDays, Mod(varTicksDifference, 864000000000));

Set(varHours, RoundDown(varRemainingTicksAfterDays / 36000000000, 0));
Set(varRemainingTicksAfterHours, Mod(varRemainingTicksAfterDays, 36000000000));

Set(varMinutes, RoundDown(varRemainingTicksAfterHours / 600000000, 0));

Set(varFormattedDuration,
If(
varDays > 0,
varDays & " days " & varHours & " hours " & varMinutes & " minutes",
If(
varHours > 0,
varHours & " hours " & varMinutes & " minutes",
varMinutes & " minutes"
)
)
);

So how i can convert the above formula into a reusable function, which accept the varNoTicks and return the user-friendly text representing the duration? so i can call this function inside each gallery Label's text representing the stages?

2) Inside the Gallery how i can find the column which have the CurrentStatus value (after removing white space), and to calculate the duration based on CurrentDateTime - PrevoiuseVersionModified ?? so for example if inside the gallery i have this "ThisItem.ForCountersignature" inside a label text, then it should show ( 24 Nov 2024 5:02 AM - 23 Nov 2024 4:02 AM) = 1 day & 1 hour .---- assuming now it is 24 Nov 2024 5:02 AM??

Can anyone advice on my above 2 points please?

Thanks

All responses (3)

Answers (0)

Suggested answer

SwatiSTW 558 Super User 2025 Season 1 on 25 Nov 2024 at 16:45:55

Like (0)

Report

Build a reusable function that show a user friendly duration based on the number of ticks()

@johnjohnPter
For your first point, you can define the formula to calculate duration in one place, like a helper variable or a hidden control. Then, instead of writing the same formula multiple times, you just use that single variable or control wherever you need the duration.

For your second point, you need to match the current status (after removing spaces) with the correct column. You can check which stage matches the current status and calculate the time for that stage. If the current stage is still active (like "For Counter Signature"), you calculate the duration from the last modified time to now. This ensures you dynamically get the right stage and show its duration
johnjohnPter 1,546 Super User 2024 Season 1 on 24 Nov 2024 at 14:05:08

Like (0)

Report

Build a reusable function that show a user friendly duration based on the number of ticks()

SwatiSTW

For Point-1, i still need to add this formula 4 times? inside each stage ? my question is how i can define the function once?

For Point-2, i still now sure how i can identify which stage column = the CurrentStatus value after removing white spaces?

i think you did not get what i am asking for exactly
Suggested answer

SwatiSTW 558 Super User 2025 Season 1 on 24 Nov 2024 at 11:28:57

Like (1)

Report

Build a reusable function that show a user friendly duration based on the number of ticks()

1. You can create reusable to convert ticks to days, hours, and minutes. You can encapsulate your formula in a reusable Power Apps function using the With() statement
With(
{
TotalDays: RoundDown(varNoTicks / 864000000000, 0),
RemainingTicksAfterDays: Mod(varNoTicks, 864000000000),
TotalHours: RoundDown(RemainingTicksAfterDays / 36000000000, 0),
RemainingTicksAfterHours: Mod(RemainingTicksAfterDays, 36000000000),
TotalMinutes: RoundDown(RemainingTicksAfterHours / 600000000, 0)
},
If(
TotalDays > 0,
TotalDays & " days " & TotalHours & " hours " & TotalMinutes & " minutes",
If(
TotalHours > 0,
TotalHours & " hours " & TotalMinutes & " minutes",
TotalMinutes & " minutes"
)
)
)
Replace varNoTicks with your input parameter or variable name containing ticks, use this function as part of your gallery items formula for duration labels.

2. To calculate the duration for the current status dynamically in the gallery, remove the white spaces from the status using Substitute(ThisItem.CurrentStatus, " ", ""). For calculating the time difference between the current date and a previous version’s modified date, use

With(
{
TicksDifference: DateDiff(ThisItem.PrevoiuseVersionModified, Now(), Milliseconds) * 10000
},
With(
{
TotalDays: RoundDown(TicksDifference / 864000000000, 0),
RemainingTicksAfterDays: Mod(TicksDifference, 864000000000),
TotalHours: RoundDown(RemainingTicksAfterDays / 36000000000, 0),
RemainingTicksAfterHours: Mod(RemainingTicksAfterDays, 36000000000),
TotalMinutes: RoundDown(RemainingTicksAfterHours / 600000000, 0)
},
If(
TotalDays > 0,
TotalDays & " days " & TotalHours & " hours " & TotalMinutes & " minutes",
If(
TotalHours > 0,
TotalHours & " hours " & TotalMinutes & " minutes",
TotalMinutes & " minutes"
)
)
)
)
Use these formulas in your gallery labels to show durations for each stage. Replace the variable names and fields (varNoTicks, PrevoiuseVersionModified) with your actual data source fields or variables. This will format the durations and dynamically compute the current status duration