•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Issue with spreadsheet containing time calculation formulas
Our take
Managing time calculations in spreadsheets can be challenging, especially when dealing with clock in/out data. In this scenario, you have an XLS file with three columns: clock in, clock out, and total time worked. While the first two columns display time correctly, the total time calculation leads to errors due to formatting issues. This guide will help you navigate these complexities, ensuring your data reflects accurate total hours worked without the frustration of error values. Let's explore effective solutions to streamline your time tracking process.
I 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
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Time In Lieu SpreadsheetHey All, I have used Google Sheets forever but my new job uses Office and I am struggling with some of Excel's functions. I'm trying to create a spreadsheet that tracks my time off in lieu (TOIL) so I know where I'm at. I've created the bare bones of the spreadsheet ash shown in the image but I need help cleaning it up. Ideally I would like to have all of the time columns (B:H) only have hh:mm format and then all of the 0:00:00 values that haven't been used not to be shown. In cell L1 I would like it to show the most current level of TOIL, again in hh:mm format. Is this possible or am I asking too much of Excel? Thanks in advance. https://preview.redd.it/c8u5ha2cxszg1.png?width=1001&format=png&auto=webp&s=3985daa3d11541abed488f377c62e375f6ab1224 submitted by /u/Silky_45 [link] [comments]
- Get rid of rounding errorHello, got a problem with a spreadsheet at work. For an calculation I need to calculate the average Full time equivalent of staff. Sheet is set up like this this: Column A: Person ID Column B: duration of period in days Column C: full-time equivalent Column D: weighted full-time equivalent for period Column E: average Full time equivalent over year Column A to C are preset base data. Formulas are in column D and E Calculation is basically done, broken down the formula is: In column D: =ROUND([duration days]/365×[Full time eqivalent for period];4) In column E it is: =SUMIF([Person ID]; [Person ID]; [Weighted for period]) But because if the rounding it doesn't add up to 1, it gies to 0,9999, even if the full year average is 1. Any ideas, how to fix that? (Other than IF(0,9999; 1; do the math) 😂) Was thinking about something like " if all Full time equivalent for period (column C) for one person ID (column A) are 1,0000, then make the average over year 1,0000, if not make the calculation based on my current formula for column E" but I can't get the operators right for that. Thanks in advance fir any help😂 Edit: moving rounding to column E instead of D did the trick. Sometimes its the obvious stuff xD Thanks to all submitted by /u/AttemptSlow612 [link] [comments]
- Small Business rounding issueHi, Can anyone identify why my spreadsheet is calculating the total to be $6133.33 instead of $6133.20? 153.33 total hours x $40/hr. I've been using the same spreadsheet for a year, the formulas are correct, this hasn't happened before, I can't figure out how to fix it. This is on sheets. Thank you https://preview.redd.it/0tzyhv5xkkxg1.png?width=708&format=png&auto=webp&s=7376ba5124c04fef83cea620b23810079a01655f https://preview.redd.it/lp1vzu5xkkxg1.png?width=702&format=png&auto=webp&s=68ca02b9d005fff97f4f69615e542d85fb02c9d1 https://preview.redd.it/5g9zi96blkxg1.png?width=1042&format=png&auto=webp&s=956143ad31c7e99cbd5551ba3f9ac9e1dff00639 submitted by /u/alicat9 [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]
Tagged with
#Excel alternatives for data analysis#natural language processing for spreadsheets#generative AI for data analysis#real-time data collaboration#real-time collaboration#financial modeling with spreadsheets#AI formula generation techniques#formula generator#machine learning in spreadsheet applications#rows.com#Excel compatibility#Excel alternatives#modern spreadsheet innovations#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions