cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
markimark50
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
Eelman Resident Rockstar
Resident Rockstar

Re: Calculating hours and minutes in debit and credits

@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.

View solution in original post

4 REPLIES 4
Eelman Resident Rockstar
Resident Rockstar

Re: Calculating hours and minutes in debit and credits

@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?

markimark50
Frequent Visitor

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

 

 

Eelman Resident Rockstar
Resident Rockstar

Re: Calculating hours and minutes in debit and credits

@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.

View solution in original post

markimark50
Frequent Visitor

Re: Calculating hours and minutes in debit and credits

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

Helpful resources

Announcements
MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,704)