Posts by TBro

    Trying to resolve myself I found something that kinda works, and if you can help me figure out the 12am/pm problem I think I might have my solution. (and if you can help me make Column Q and R reflect the date and time rather than Excel's stored number for said date and time that would be awesome, but not absolutely necessary.) I've attached my in-progess solution.

    Sorry I wanted to add an Edit but it froze up on me and won't let me edit again.

    If I'm reading your formula correctly: If the last two characters are "am" then substitute them with blank. Since that's not what is happening in the cell, I'm not sure where the am/pm is coming from in the N column so I don't know how to fix the 12pm/12am issue.

    Thank you for your quick response Carim! While checking the file, for some reason N4 is showing 12am rather than the 12pm showing in G4. That's the first one I noticed but the same goes for N14 and (I believe) any of the 12pm start times (and possibly end times). With that said, it looks like you are pretty close to solving my conversion issue. Any help on the formula to determine which is the "bad egg" so to speak? After conversion I attempted a simple =if(N2<O2,"good","bad") but it gives a TON of false negatives obviously because it doesn't have a date to reference since the End Date would go with End Time. Maybe a way to concatenate Date and Time in the fields? Thoughts?


    I'm going to attach a file that I would appreciate some help on. A little background first:

    This file is a scheduling document made to import so the formatting of it's current data cannot change, however adding columns to the end and running formulae there is fine. There are no issues with this scheduling document, but when importing sometimes the importing tool finds a mistype where the finish time is before the start time (I/E Start time is 9am and the End time is 6am but should be 6pm) and will not import... the problem there is that now I'm stuck with determining where that error actually is in the document and correcting it. To compound this issue, the time is in text format, and no matter what I do (TIMEVALUE,TEXT,etc) it will not convert to a actual time. Converting to the time is my way of attempting a solution, so it may not be the best solution and I'm open to suggestions there. But to sum up, I need something that:

    1. looks at the Start Date compared to the End Date to confirm they match or the END date is only 1 day later
    2. looks at the Start Time compared to the Finish Time to confirm that the Finish Time does not come before the start time

    This may be confusing, I'm not sure, but the people who answer here are generally smarter than me and figure it out lol. I thank you in advance, if you have any questions to clear it up I would be happy to answer.

    NOTE: the site won't take a CSV file, but the file has to be CSV to import, I've attached a XLSX file.

    I believe this is going to work out fantastically... As for the fact that it needs to be rebuilt, I get it, and if I could with my current capabilities, I'd already have done so. I will absolutely come back Monday to confirm if everything worked as intended :) Thank you very much Carim, you assistance has be invaluable! I believe I placed the new code where you suggested, here's the monster currently.

    So that would check if the current day is "Monday" then pop a box that says "select 1/2/3" and 3 would be Friday, 2 would be Sat, 1 would be Sun. If I understand correctly, that would be awesome... My only question is, do I just input that on the first line of the code or does it need to be placed somewhere else?

    I do believe that will work! I tested it and it worked correctly, but the real test will be when I do it tomorrow for real. The only other issue I have, would be that I do Friday, Sat, and Sun reports on Monday. I can manually do that by changing the (Date - 1) to Date - 3 then 2 then 1 respectfully. That is, unless you have a suggestion that would fix that as well, which would be awesome but as long as this works, I will NOT complain at all lol. Thanks Carim!

    I would manually have to update it at that point anyway as this report is daily. I'm already manually updating it so I can continue doing that I suppose. Just casting a line to see if I caught a workable resolution. As I said before, maybe it isn't possible. Or if it is possible, beyond my current ability.

    Unfortunately not... I think the issue lies in the fact that it's a "quoted" file name. But I honestly have no clue and that may not mean anything lol. But I noticed I can't use anything that would constitute a formula within it... (I've tried date-1 just to see if it would work) but it just won't function. Maybe this isn't even possible.

    This may or may not help, but I would suggest making 2 rows titled "Total" and "YTD" at the bottom of each column. use =SUM in the total field and reference the rows above it. Then in YTD use SUM (or just simply add) the previous month to the month following to create your YTD. Then use INDEX Match to index the YTD row and Match P01 - P##. I created a sample and attached it in case I didn't explain correctly:


    I am fully aware of how ugly this code is going to be as it's a recorded macro and I am by no means a master. I figure my question seems to be a very simple one, but I can't seem to find an answer for it. Basically I am attempting to get my macro to reference a new date in a file name (I/E: Report 02-04-2020 will be Report 02-05-2020 tomorrow). As of right now, the macro will only reference the exact date that is manually entered. (basically I go in every day and change it to the previous day's date). I have tried using *'s/?'s/*.*'s in place of the date in hopes it would work to no avail. I feel like this is a really simple resolution but I can't seem to find it. Here's my code line 11 being the issue I'm trying to resolve: