# Posts by TBro

• ## Converting Text to Time

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.

• ## Converting Text to Time

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.

• ## Converting Text to Time

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?

• ## Converting Text to Time

Hello,

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.

## Files

• Sample.xlsx

• ## Dynamically Reference new date in File Name daily

Apologies, I haven't come back to let you know that the macro is working as designed... I have another odd issue but I'm going to post that in the formula's questions. Thank you very much Carim, you were a big help!

• ## Dynamically Reference new date in File Name daily

I tried to edit my previous post here, but just to let you know, I tested your code on today's date with Tuesday's data up and changed to "Friday" in the code and it looks like it's working great! Thank you!

• ## Dynamically Reference new date in File Name daily

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.

• ## Dynamically Reference new date in File Name daily

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?

• ## Dynamically Reference new date in File Name daily

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!

• ## YTD calculations with multiple criteria

Guess my suggestion didn't work lol

• ## Dynamically Reference new date in File Name daily

Then that may fall into the criteria of beyond my current capabilities. I'm not sure how to incorporate that information into my macro is the problem.

• ## Dynamically Reference new date in File Name daily

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.

• ## Dynamically Reference new date in File Name daily

Assume I'm an ignorant fool... I'm not sure what those formulas will do. I do apologize.

• ## YTD calculations with multiple criteria

Apologies, minor correction to the sample spreadsheet. Was adding the wrong cells together for the YTD:

• ## Dynamically Reference new date in File Name daily

the file name is "Shifts Scheduled 02-04-2020 - Final.xlsx"

• ## Dynamically Reference new date in File Name daily

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.

• ## YTD calculations with multiple criteria

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:

• ## Dynamically Reference new date in File Name daily

Code
1. Windows("Shifts Scheduled 02-04-2020 - Final.xlsx").Activate
• ## Dynamically Reference new date in File Name daily

I know, line 11 shows the method I *attempted* to resolve based on Google Research. I can change it, or assume that it's yesterday's date and I need it ready for today's date.

• ## Dynamically Reference new date in File Name daily

Hello,

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: