I am working on a project where i need to extract data based on time and date conditions; between certain dates and before or after specific times each day. One column is the date, the next column is the time (24 hour) and the third column is the information i need. I am using a nested if statement:
where $F$1 and $G$1 are the time constraints for the time values in column C, while F$2 and G$2 are the date constraints for the dates in column B. the value i need to extract is in column D, and the function iterates to the right from F3:F through Z3:Z . the date conditions work 100% while the time conditions work inconsistently.
the function returns the correct values for all times within the constraints before 12:00:00 PM for the proper date range. after that time, it returns the value from column D even though the time constraints are not met. i have tried converting the time column to number format; times before 12:00:00 PM resolve correctly but the times after 12:00:00 PM do not. more so, i cannot change the format of these times after 12:00:00 PM; even custom formats do nothing to change the way it is displayed. i technically can use text to columns and then use a formula to convert the time to a number format but this is very inefficient and a huge time suck. what am i not understanding here? the original CSV file was dumped from MSSQL server.
i have attached a sample sheet demonstrating my quandary.