I am working with an excel spreadsheet of traffic counter data for three lanes of traffic in a street. The lanes are coded as "SB1", "SB2", and "SB3," and are all contained in one column. Unfortunately, the timestamps for these transmissions are not coded as either AM or PM. There are two identical timestamps for each lane, but they are definitely different datapoints. They are in such a consistency that I can create an automatic list that reads "AM" and then "PM" in every other cell.

To ensure optimal accuracy, I wish to run such a list when my workbook is filtered for one of the three lanes (SB1, SB2, and SB3). That is, I want "AM" for every other row that contains "SB1", and "PM" for all remaining rows that contain "SB1."



Does Excel have that capability? Do I have to write a macro to do this?

Attached Files

traffic.xlsx (15.2 KB, 11 views)

It's not at all apparent to me how your AM/PM times are differentiated. When I filter on SB1, I see that your 'AM or PM' column has nothing for the first few rows, then 2 PM entries followed by 5 AM entries, 2 PM entries, 1 AM entry, 1 PM entry, 2PM entries, etc. I can't see how this relates to:
In any event, to convert a time in column C from AM to PM, just add 0.5 to the time value there. To do that, input 0.5 into any cell, copy it, then select the cell(s) you want to update and choose Paste>Paste Special>Values>Add>OK.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]