•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
VBA parsing string to calculate
Our take
In dispatch operations, accurately parsing and calculating date and time data is crucial, especially when working with complex formats like Year and Julian Date (YDDD)/(HHMM). This process often involves converting strings into usable date-time formats, performing calculations such as adding hours or subtracting time, and then reconstructing the string for seamless integration. Utilizing VBA or Python within Excel 365 can streamline this workflow, making it more efficient and foolproof for dispatchers.
VBA 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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 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]
- 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]
- Complicated matrix with buggy VBA. Don't know what went wrong, much less how to fix it :(Hi! Hope this is acceptable here. I'm looking for someone to troubleshoot some VBAs. I think this is the right terminology? I work for a small county government. Part of my duties are to track and coordinate training for our elected officials. With 5 of them, plus myself, it can be a lot. I'm a visual person, so I created this matrix. I want it to do a few things when I eventually throw it up on a projector for them: show what they've taken to date, show what classes are most efficient to take, and demonstrate future changes by toggling a class on or off. I'm pretty good with technology, but Excel is not in my wheelhouse. Full transparency, I ChatGPTed my way to this point. I told Chatty what I wanted this spreadsheet to do, and it walked me through the VBAs. The top part works fine for the primary tracks. It's the crossover hours section that's faulty now. I want it to turn classes they've taken blue. Once they've satisfied that third crossover class for a specific track, I want it to essentially disqualify all the others by turning them purple. For what it's worth, it was working like a gem (a bit slow, but working nonetheless) before I had to go back in and add some rows. Now it's a mess. Is there anyone who can help me here? I was so proud of this baby, and it was actually a useful tool! I was so close to being able to use it with commissioners in a group setting. Now, I don't know even what to fix, much less how. I tried ChatGPT again, but no luck. I'm at the point I need a human! Happy to chat if you need more info or the file itself. https://preview.redd.it/5viqmc0uw2jg1.png?width=2372&format=png&auto=webp&s=34e01647e1492e84b616c2338df97867b50f14eb https://preview.redd.it/rbnfdd0uw2jg1.png?width=2373&format=png&auto=webp&s=b6dffc4a18cd7ff52fb744713a8010c07d4a1a80 submitted by /u/laundry_loather27 [link] [comments]
Tagged with
#Excel alternatives for data analysis#real-time data collaboration#real-time collaboration#generative AI for data analysis#natural language processing for spreadsheets#Excel compatibility#Excel alternatives#financial modeling with spreadsheets#rows.com#big data management in spreadsheets#conversational data analysis#AI-driven spreadsheet solutions#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#VBA#string parsing