•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Calculate hours and minutes correctly
Our take
Calculating worked hours in Excel can be tricky, especially when dealing with time-formatted data. If you’re encountering issues where formulas return zero for times like "6:00" or "8:30," it may be due to the way Excel interprets time. The good news is that you can streamline your calculations without changing the format. Consider using the `SUM` function or adjusting your formula to ensure it correctly recognizes the time format.
I'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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How Do You Change a Cell's Value Based on Another Cell?At work, we have a spreadsheet where we track sales and employee hours from another system. We manually enter the sales and the time. However, the columns are separated in an hours and minutes value. Whenever I enter hours, I have to do the math manually. Not super hard, obviously, but I'd like to work smarter and not harder. If Hour 1 has total labor of 2 hours, 30 minutes, I'd enter that manually. If Hour 2 has total labor of 2 hours, 45 minutes, I'd like for it to automatically set the Hours and minutes to the correct one. So, the system would see 4 hours, 75 minutes and change the hours and minutes column to be 5 hours, 15 minutes. Again, the math is what I can do manually. I'd just like to have it be automatic. Thank you. submitted by /u/ChronoKeep [link] [comments]
- 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]
- 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]
- How to calculate elasped time in excel when date and time are in the same cell?I have series of data in the below format in excel. I want to calculate the elapsed time. I have tried multiple different way but nothing seems to be working. Format: MM/DD/YYYY hh:mm:ss Column: C4 - C2500 ____________________________________________ Formulas I have tried: Flash entery Separating time into a different column (column D) and calculating batch hours ( column E) with this formula =(D4 - $D$4)*24 (Note: problem with 2 option is, after 12 hours period the batch hour goes to negative instead of getting added continuously. I did check the column format for column D and it was set 'hh:mm:ss') Please help, I need to get this sorted soon 😭 Thank you submitted by /u/Shining_Swan [link] [comments]
Tagged with
#Excel alternatives for data analysis#real-time data collaboration#generative AI for data analysis#Excel compatibility#financial modeling with spreadsheets#real-time collaboration#Excel alternatives#rows.com#natural language processing for spreadsheets#big data management in spreadsheets#conversational data analysis#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#calculate#worked hours#excel