cancel
Showing results for
Did you mean:
Highlighted
Level: Powered On

## 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
Level 10

## Re: Calculating hours and minutes in debit and credits

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
Level 10

## Re: Calculating hours and minutes in debit and credits

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?

Level: Powered On

## Re: Calculating hours and minutes in debit and credits

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

Level 10

## Re: Calculating hours and minutes in debit and credits

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.

Level: Powered On

## Re: Calculating hours and minutes in debit and credits

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

Announcements

#### 'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

#### Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,156)