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.

  • 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?

  • 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.

  • 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.