Adam has attended:
Excel Intermediate course
Excel Advanced course
Access Introduction course
Calculating time
I have a rota programme that calculate the amount of hours automatically. But I have to enter for example; 10.75 in the cell A1 and 15.25 in cell B1 for the progrmmae to calculate that the employee worked for 4.5 hours by simply using the formula B1 - A1. This obviously leads to the employees occasionally coming at the wrong time. How do I get it to calculate the amount of hours using proper time?
RE: Calculating time
1) Time should be formatted as hh:mm
2) Time 10:75 will automatically recalculate to 11:15, which gives you the time difference as 4hr 10mins
3) Cell A1: 11:15 (format in Time)
Cell B1: 15:25 (format in Time)
Cell C1: A1-B1 (format in Time)
Cell D1: =HOUR(C1)+MINUTE(C1)/60 [General format]
OR Cell C1: =HOUR(A1-B1)+MINUTE(A1-B1)/60
[General format]
See if that works
Katie
RE: Calculating time
Hi Katie,
all teh time formats include seconds. The function of this sheet is for a rota to automatically calculate the hours someone has worked.
It would be very laborious to enter or see seconds on the rota, yet their is no time format without seconds as indicated on your answer.
Let me know if you think this can be rectified.
Regards,
Adam
RE: Calculating time
You can customise TIME format.
1) Bring up format cell window;
2) Choose CUSTOM category
3) Type in your preferred format:
hh:mm (h-Hour, m-Minute, s-seconds)
Let me know if you need more help
Katie
