Need to Reformat a Negative Time
Our take
Hello 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!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- VBA parsing string to calculateVBA de-converting a string, doing a computation, and reconverting For a dispatch operation we grab dates and times in the format in the attached photo. Running with GMT timing. Format is Year and date (YDDD)/(HHMM) (2026 Feb 26)/(1545z) which is the last number of the year, and the Julian date for number of days in the year has passed. The issues I’m running into trying to keep it tidy and foolproof for my other dispatchers, is finding a way to have excel parse the date time group in this format, and do a +12 hour calculation The second set of two lines is when we have something leaving and when a subtracted time to start working on that. -3 hours 45 minutes as an example. It’s a data entry driven process for the left column where we copy paste those entries, and I want them to auto populate in the right column I need to avoid multiple columns, and need date and times combined and need to avoid using local times (as this would need a manual time conversion into the sheet) and standard time subtraction runs into issues when I have negative times and it doesn’t roll over. I haven’t been able to get =-/+TIME(3,45,0) breaks when I have Julian date and time separated and the HHMM rolls over it throw an error I believe VBA or maybe python (running on Excel 365) could get me there, but I am at loss of how to deconstruct the string, have excel do the date and time conversation, and reconstruct the string in the format it was input with. submitted by /u/WoundedAce [link] [comments]
- 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]
- 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]
- 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]