The ordinary timepiece or clock has a base of 60 units (calculated in seconds or minutes) whereas mathematical numbers have a base of 100.
What does this mean?
When you say it is half past three, your clock shows 3.30 (am or pm).
In decimal terms, it would mean 3.50.
How can we convert 3.30 into 3.50 in formula terms on and Excel Sheet?
Here is an example:
Time In: 09.00 am
Time Out: 12.30 pm
Time worked: 03.30 hours
When you subtract 9.00 from 12.30, the answer, based on mathematical calculation, would be 3.30. This is at a base of 60, and not 100.
If you make a payment to an employee for 3.30 hours, it will not be correct. It should be for 3.50 hours.
The base 100 should give the result of 3.50, which means three and a half hours.
Insert the following formula in cell C10 on the Spread Sheet as shown here.
You can also calculate the total time spent by each department and work centre.
To calculate the total time spent by each department, write the following formula in Cell U10:
=SUMIF($F$10:$F$20,T10,$E$10:$E$21)
Similarly, insert the following formula in Cell W10 to calculate the total time spent by each work centre:
=SUMIF($G$10:$G$20,V10,$E$10:$E$21)
Mukesh Arora is Director of Tally Accounting & Business Solutions Limited. He has Chartered Accountant qualifications in New Zealand and Fellow Chartered Certified Accountant (UK) and Cost Accountant (ICWA) India. He can be contacted on 0800-825599 or 021-1290810 Email: tallysolutions@xtra.co.nz
