Time Calculations in Excel – how can I use cell formatting to allow use of these in spreadsheets? Watch the video to learn more
This week’s hint and tip is about time calculations in Excel. This looks at how you can use custom cell formatting to enable you to carry out time calculations. This isn’t covered in great details in any of our courses so we decided to do a hint and tip on it. We are going to go through it now below.
Why would you want to do time calculations in Excel?
Time calculations in Excel can often be used in spreadsheets where people clock in and out for their working day. Having a certain custom format for the clock in and out times can then allow calculations to be carried out. These calculations can then be used to work out the total hours worked for a group of people.
Custom formatting you have to use
To work out time calculations, you firstly need to ensure that the correct cell formatting is being used. The correct cell formatting being :mm. This cell formatting can be found in the custom section in the cell formatting area. If it is not there you can type it into the ‘Type’ box.
Important difference between h:mm and :mm
As mentioned above the formatting used in the video is :mm formatting allows for working out a time calculation across days so it allows for if a person has worked longer than 24 hours. Whereas the h:mm formatting is designed for ‘clock times’ and so won’t go above 23:49 as a length of time.
The time calculation to use
For carrying out time calculations, you first need to make sure that the formatting is correct for the clock in and clock out times. Once you have the formatting correct, the calculation is straight forward. All you do is take the clock in time away from the clock out time. For example, if the clock in time is in B2 and the clock out time is in B3 then the calculation would be =B3-B2. The reason it is straight forward is because the formatting has been selected to enable this to occur.
The video below shows you how to change the formatting in Excel and then how to carry out time calculations in Excel. This feature in Excel can be very useful for working out how many hours a person has worked.
Take a look below at the video to find out more and then try it out on your own computer!
We hope you have enjoyed this hint and tip on time calculations in Excel. Why not take a look at our previous one on getting data from a PDF document?