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

@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
Super User
Super User

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

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

 

 

@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

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Top Kudoed Authors
Users online (57,686)