How to subtract hours and format time in excel

Hey everyone! 👋 If you've ever found yourself scratching your head trying to subtract time or get your Worked hours to show up correctly in Excel, you're definitely not alone. I remember how frustrating it was when my time calculations just wouldn't add up, or when I ended up with weird numbers instead of actual hours. But good news – it's actually pretty straightforward once you know the right formula and formatting! I'm here to share how I tackle this in my Excel File for daily tracking. Let's dive into the core of subtracting time. The basic formula is simply End Time - Start Time. For example, if you have a Time In in cell B2 and a Time Out in cell C2, your formula to calculate Worked hours would be =C2-B2. Excel stores time as a fraction of a day, so 1:00 PM is 0.541666..., and 6:15 PM is 0.260416... When you subtract, you get another fraction. The key is to tell Excel to display this fraction as actual time. This is where formatting comes in! After you've entered your subtraction formula, your result might look like a decimal number. Don't panic! Just select the cell(s) with your time difference, right-click, choose Format Cells..., and then go to the Number tab. Under Category, select Custom. Here, you can type in h:mm to show hours and minutes. If you're dealing with durations that might exceed 24 hours (like total project time over several days), use [h]:mm – the square brackets tell Excel to sum hours beyond 24 instead of rolling over to a new day. This is super helpful when you're looking at total Hours spent! What about those Time In and Time Out scenarios you often see, like in the image where Steve worked from 1:00 PM to 6:15 PM? You'd simply put 6:15 PM in Time Out and 1:00 PM in Time In, then apply the =C2-B2 (or whatever cells you're using) formula and format as h:mm. For Jim, who worked 8:00 AM to 4:10 PM, the same logic applies. This helps you quickly calculate Worked hours for each entry on your Sheet1. One common question I get is about 24-hour format. If you input Time In and Time Out using the 24-hour clock (e.g., 13:00 for 1 PM, 18:15 for 6:15 PM), your formula remains the same: End Time - Start Time. To display the result in 24-hour format, use a Custom format like hh:mm or [hh]:mm. This ensures consistency, especially in professional Excel File setups. Now, for a common hiccup: negative time results. If you try to subtract a later Time Out from an earlier Time In (e.g., working overnight, 1:00 AM to 10:00 AM the next day), a simple =B2-A2 will often give you ############ or an arithmetic overflow error if you're not careful. This happens because Excel's default date system (1900) doesn't handle negative times directly. My trick for this is to add 1 to the End Time if it's less than the Start Time (assuming it crosses midnight). So, the formula for subtracting time in excel for overnight shifts would look something like =(B2+(B2<A2))-A2, where B2<A2 evaluates to 1 (true) or 0 (false), effectively adding a day when needed. Then, just apply the h:mm or [h]:mm Format Painter to get your correct Worked hours. Mastering these little Formulas makes a huge difference in efficiency!