Convert number to time

[Barcat]

Hi all,

I have created a formula to calculate the difference between Budgeted Hours and Billable Hours. The cels are on Number (2 dec positions) format.

For a moment I thought that everything is ok, but when I did some tests I thought an error. And I do not know what I need to do to correct it.

My formula is: =FLOOR((B1 - FLOOR(B2) - (MOD(B2, 1) * 10/6)) ) + ( MOD((B1 - FLOOR(B2) - (MOD(B2, 1) * 10/6)), 1) * 6/10 )

BUDGET HOURS	20.00
BILLABLE HOURS	0.30
FORMULA	19.3
BUDGET HOURS	19.30
BILLABLE HOURS	0.30
FORMULA	18.48

The formula used on second run just changes the cells.

=FLOOR((B5 - FLOOR(B6) - (MOD(B6, 1) * 10/6)) ) + ( MOD((B5 - FLOOR(B6) - (MOD(B6, 1) * 10/6)), 1) * 6/10 )

I know why the in the second run the value was 18.48, but I expected 19.00

[#2]

@Barcat 

Why not simply use time values instead of numbers with decimals, and avoid all these problems?

[#3]

Hi @Hans Vogelaar ,

The system that my customer uses has a limitation and I cannot do these calculations in a time field. So I need to do these difference with integers. The best way is to work with Time, but it is not a reallity here at this time.

[#4]

@Barcat 

Try

=INT(B1)-INT(B2)-(MOD(B1,1)<MOD(B2,1))+MOD(100*(MOD(B1,1)-MOD(B2,1)),60)/100

and

=INT(B5)-INT(B6)-(MOD(B5,1)<MOD(B6,1))+MOD(100*(MOD(B5,1)-MOD(B6,1)),60)/100

[#5]

@Barcat 

Oops, the formulas in my previous reply don;t work correctly if B1 is less than B2. Try this instead:

=SIGN(B1-B2)*(INT(MAX(B1,B2))-INT(MIN(B1,B2))-(MOD(MAX(B1,B2),1)<MOD(MIN(B1,B2),1))+MOD(100*(MOD(MAX(B1,B2),1)-MOD(MIN(B1,B2),1)),60)/100)