Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
now. You should be looking at this example of a timesheet. Working with the addition and subtraction of time inside a spreadsheet like Microsoft Excel should be as easy as pie, but sometimes you can really pull your hair out. Usually those are the times when you've got your cell formatting incorrect. For example, this simple formula found here in C18 will do the math on Monday, January 12th; in at 10 AM, out at 5 PM, 7 hours worked, straightforward. Now if I had that cell formatted incorrectly. Let me just change this. Say, for example, I was using a time format, such as this one, now it's going to look like just any other time value on my sheet. Well we're not going to fall into that trap I know, but your first instinct would probably be to switch it to a time like this. Well, now that looks right. The problem with that time format is it doesn't handle any more than 12 hours. For example this simple formula sums the values found in this row and adds them here. Well it's obvious that 8 and 8 and 8 is 24, plus 4 is 28, plus 7 1/2 that's 35 1/2 hours, and this employee is only getting credited for 11 1/2. The reason has nothing to do with the formula that we're using. It has everything to do with the formatting on the cell. If you want to allow your time values to stretch beyond the 12 hours, you need to set the format as such. Let's go down to Custom, this time value, with the h in brackets uses 24 hour time and will allow a time value to exceed even 24 hours. Unfortunately we don't want the sections included, and the @ symbol is extraneous, so Im just going to back that up and we'll use that, and there we go; 35 1/2 hours. So the key to having the math work was having that cell formatted correctly and putting the h in square brackets allows that cell to display a time value greater than 24 hours. Now let's go back down here to C18. This simple formula for calculating the hours worked in a day, it works fine when you're In in the morning and Out in evening, which is going to cover 99% of everybody's workday anyway. But what happens if you're working nights, where you start in the evening and end in the morning. For example, if 10 AM were to become 10 PM, this formula falls apart. Subtracting the In time from the Out time falls apart when the In time seems to be greater than the Out time. That's where your If statement comes into play. Take a look at this formula. This formula says is your Out time less than your In time? If that's true, then add a full day to your Out time, and then subtract your In time, then you'll get an accurate result. If it's false then just go ahead and subtract your In time from your Out time. With this intelligent conditional statement, you can handle time values that seemed to start before they end.
Let's copy this formula down to the cell below. Copy and Paste. Because they were relative cell references, it's updated all of the cells involved in creating this formula. As you can see we're looking now at C16 and B16 and the conditions that apply there. Now starting at 10 PM the night before and finishing at 5 PM the day after, whew that's a long shift, but at least it's accurately calculated that if you have worked a 19 hour shift and of course another key to making sure this formula never breaks down is to format the cell with the square bracketed h followed by your minutes.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74357 Viewers
80 Video lessons · 129634 Viewers
52 Video lessons · 63895 Viewers
59 Video lessons · 49678 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.