cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculating hours and minutes in debit and credits

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

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.

4 REPLIES 4
Super User

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?

Frequent Visitor

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

Super User

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.

Frequent Visitor

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

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!