Answered

Calculating hours and minutes in debit and credits

(0) Share

Report

Posted on by markimark50

We currently have an app where a user enters their start and end time and this is then deducted from 7 hrs 24 mins,(working day) if they work over this they are in credit, if they work less than this they are in debit.

We have the following formula that calculates the difference from start to end and then works out the hours and minutes in debit or credit by deducting the 7 hrs and 24 mins.

RoundDown(minutesCreditDebitNumber/60,0)& ":" &Mod(minutesCreditDebitNumber,60)

If the user is in debit the formula calculates the hours and minutes correctly, however if the user is in credit it calculates the hours correctly but not the minutes.

An example is if the user has worked 8 hours in a day (minus the 7:24 which is the working day) they should be 36 mins in credit, however the formula is returning a value 24 mins in credit.

Can anyone advise what the correct formula would be to calculate the correct minutes in credit.

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@markimark50
What other examples have you tried? Can you post some more inputs and outputs? Also, how is minutesCreditDebitNumber calculated, can you share that formula ... or is it your 7hrs 24 mins?

Was this reply helpful? Yes No
markimark50 8 on at

Like (0)

Report
Copy link

Link copied!

Hi
minutesCreditDebitNumber is 444(This is 7 hrs 24mins)-minus totalDailyMinutes (this is the total time worked in minutes that day)

We can't understand why the calculation works then the user is in debit but not in credit.

Thanks

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

@markimark50
Ah ok, it's because Mod() gets confused with negative numbers.

Eg When a person works more than 7:24 (say 8hrs) totalDailyMinutes = -36 then in the Mod function things get a little confused. I can explain it further if you like?

To fix your formula wrap your minutesCreditDebitNumber inside your Mod() in the Absolute value function, like this:

RoundDown(minutesCreditDebitNumber/60,0) & ":" & Mod( Abs(minutesCreditDebitNumber), 60)

The Abs() function will just remove the negative sign for you, effectively working on a positive number, which is what you want Mod() to do.

Was this reply helpful? Yes No
markimark50 8 on at

Like (1)

Report
Copy link

Link copied!

Thank, that has worked perfectly, you've saved us hours of head scratching! 😀

Was this reply helpful? Yes No