•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Why is my hrs. Worked showing negative number?
Our take
If you're seeing negative numbers for hours worked in your overtime tracking spreadsheet, you're not alone. This issue often arises when Excel misinterprets time spans that cross over midnight. In your case, using the formula =(A2-A1)*24 is correct, but it doesn’t account for the date change. To resolve this, ensure that your end time reflects the correct date. Specifically, you'll need to adjust the formula to correctly interpret the transition from 3:00 AM to the following day.
I making a spreadsheet to help track overtime
I have one column labeled DATE formatted as a date mm/dd/yyyy Then I have a TIME START and a TIME END COLLUMN
I am using formula =(A2-A1)*24
For example
Date 2/10/2026
Start time : 10:00 PM TIME END : 3:00AM HRS WORKED: -19
How do I make it so excel knows the 3AM is from 2/11/2026
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Issue with spreadsheet containing time calculation formulasI have a new clock in/out machine which has 3 columns, 1 for clock in, 2 for clock out and a 3rd column for the total time worked. Staff can clock in/out multiple times a day. It comes to me as an xls file with all the in/out cells as 'general' format and the 3rd column is as custom hh:mm but not the subtraction formula to show the total time worked even though the numbers are right. If i change the in/out from general to custom and then hh:mm I seem to have to click each individual cell for the format to change from (ie) 09:58 AM to 09:58 without the AM but the PM cells still show PM and when i do the subtraction formula for the total time worked it comes out as an error value, no amount of trying different formulas will change it to what it needs to be. I don't mind the first 2 colums showing as AM/PM but even with them that way the subtraction for the total time calculation still comes up as with a 'value' error https://preview.redd.it/hvunwr4p58og1.png?width=512&format=png&auto=webp&s=c11e3a1b77c137a4a40544c7bb2274e813d1038c submitted by /u/Loud-Conference8055 [link] [comments]
- How to calculate hours worked per pay period per employee?I am helping with making a spreadsheet to help HR keep track of the OT hours each employee works each pay period. My spreadsheet has the following relevant columns: B: Name H: Date I: Week # J: pay period (calculated biweekly) K: time start L: time end M: total hours I am trying to create a summary page that will list the employees and then the pay periods (1, 2, 3.....) and just show how many hours they worked each pay period I tried doing a pivot table, but it did not look how I wanted Also, for column J, how can i make it so if there is not a value on column H it does not show -3286 here is the formula i used for the pay period =INT((H6-DATE(2025,12,14))/14)+1 submitted by /u/stabycat [link] [comments]
- Calculate hours and minutes correctlyI've got a small issue when trying to calculate "worked hours" In excel the data is formated as "time" and the cells have "6:00" "6:30" "8:30" but when I try to use formulas to calculate the total worked hours I keep getting 0 as results. If I change to , instead of : it works but there's constantly new hours added into the sheet and I wonder if there's some kind of fix for this issue? Tldr: want to calculate hours and minutes, formated as time and with : It works with 6,00 8,00 etc but not 6:00 8:00. submitted by /u/JDaleth [link] [comments]
- Need to Reformat a Negative TimeHello all! Working on a bunch of data at work, and I've got most of the nuts cracked except this one. Because I'm trying to consolidate weeks worth of data and look for patterns, I've got a 14 page workbook. My ultimate goal is to make this user friendly for others to use with only copying / pasting their data into the data sheets. The data that we're importing is bad. It's got multiple numbers and times stored as text. I've gotten them all to work (figured out how to copy a cell and select ranges of numbers and paste special multiply in VBA today, so that was cool!) except for one. The cell is a difference in time, but I don't have the original numbers, so I can't even perform the calculations myself. I've just got a cell that says (for example) -00:08:37 for 8 minutes and 37 seconds under plan. When the cells are positive, I can multiply them by 1 then format them back to hh:mm:ss, but when they are negative this isn't working. I could likely use some sort of a formula to break them down and turn them into a decimal, but like I said, I'm trying to make it where other users can paste data (up to 12 days of up to 200 rows x 25 columns) and then hit a macro button and have it fill / filter all the data for them. As such, I'd really prefer to avoid using a second column to make this happen, and that's where I'm drawing a blank. So what I'm hoping to do is use a VBA function of some sort to change -hh:mm:ss into -mm.ss or similar format, preferably within the same column. I've only seen one number where hh = 01, but it has happened. 99% of the time it's -00:mm:ss. Excel 365 desktop in English. I'm probably intermediate / advanced. I'm already using macros in this workbook. Thanks all! submitted by /u/JustPlainJef [link] [comments]