I need help with Excel

Question

I have time in a row that increments by 5 minutes from 8am to 21:30. In a second row I have some time. In a third row I want a yes in the same column of row one if time in row two matches. (=IF(COUNTIFS(C8:C21,13:05), "YES", "NO")) did not gave correct results. I use Excel 2007

Answer 1

The times that you are bringing into Excel may be coming in as text. That will keep Excel from recognizing them as a match even though they look the same. You can use the TIMEVALUE function to convert them to a number value and then format them as time. Here is an illustration. I entered the times in Row 2 as text and used the formula in Row 3 to convert them to number values formatted as time.

If you are bringing them in with a formula you can use the TIMEVALUE function to convert them as you bring them in. If not, you can use the formula in another Row/Column to convert them, paste values over the formulas to get rid of the formulas, copy and paste the new data over the incorrect data (make sure the cells are formatted as General before pasting), and then delete the data in the cells where the formula was.

Answer 2

Hi Rich,

Thank you very much for your feedback. I ran tests and noticed something strange.

I created my own range time series and my formula works well.

I copy time and date from an external log file and extract the time in Excel.

Something prevents Excel from recognizing some times even though the format looks the same.

I hope you can help me. I am attaching photos

Regards

Louis

Answer 3

I don't have a full picture in my mind of your layout, but I do see an issue with your formula. The logical test in your IF function with the COUNTIFS function doesn't have an element to produce a TRUE or FALSE result. If I understand what you are wanting to do with the formula you can try this modification.

=IF(COUNTIFS(C8:C21,13:05)>0,"YES","NO")