casInterest From United States of America, joined Feb 2005, 5776 posts, RR: 3
Reply 4, posted (6 years 2 months 1 week 4 days 22 hours ago) and read 4021 times:
You forget that you are using two different units values to multiply into $/hour. if you truly want $ per hour as your multiplier you need too have the time entered as hours. Think of this as a modulus operation on the true number.
Hours of the timestamp+Minutes of the Timestamp converted to hours by dividing by 60
I ran a sample listed below.
I offer up the following: Where A is your Time format in X:X and B is your Cash
Petertenthije From Netherlands, joined Jul 2001, 3559 posts, RR: 10
Reply 6, posted (6 years 2 months 1 week 4 days 22 hours ago) and read 4019 times:
I use the Dutch version, so the exact term might be slight different.
Say you got your begin time in cell A1, end time in B1 and the total time in C1 (=B1-A1).
You have two options, one simple one a bit longer:
A1 = 10:00
B1 = 12:30
C1 = 2:30
D1, add funtion "=HOUR(C1)", result will be "2", multiply this by the price per hour so you get "=HOUR(C1)*850". You'll get 2*850 = 1700.
E1, add funtion "=MINUTE(C1)", result will be "30", divide this by 60 (minutes) and multiply by the price per hour so you get "=(HOUR(C1)/60)*850". You'll get 0,5*850 = 425.
F1, add function =D1+F1". You'll get 1700 + 425 = 2125.
Or you can combine the function in cell D1 and E1 into one big function: "=(HOUR(C1)+(MINUTE(C1)/60))*850". If you really want to make your sheet future proof you might want to consider putting the price per hour in another cell and letting the formula refer to that cell. If the price per hour changes, you only need to change the input cell and not all functions.