Writing this Excel task taught me a lot of nice tips and tricks! The job was: A comma delimited file had a column of times not quite in a standard format, the times were for "on" and "off" events, I had to total the elapsed time between each "on" and "off". To do this, I had to decode the timestamp into Excel time format, verify that each on and off matched by comparing that the other column contents matched, and then add each total time.
New column 1: The source column has timestamps in this format: "2012/03/29_11:06:46" Set cell format of the new column to Date expressed as 1/1/2001 13:30. Then use this formula:
=DATEVALUE(MID(E3,1,10))+TIMEVALUE(MID(E3,12,8))
The following links contained all the information needed to figure this part out:
http://superuser.com/questions/274494/convert-text-string-to-date-time-format
https://exceljet.net/formula/convert-date-string-to-date-time
New column 2: Compares the "ON/OFF" column in the current and adjacent rows, along with the other identification column data to determine if the ON event matches the off event:
=IF(OR((AND(D3=$N$1, B3=B4,C3=C4,F3=F4,G3=G4,H3=H4, D4=$O$1)),(AND(D3=$O$1, B3=B2,C3=C2,F3=F2,G3=G2,H3=H2, D2=$N$1))),"Matched","Oops")
$N$1 and $O$1 contained the strings ON and OFF for comparison purposes. There was a much harder way to do this without just creating cells to compare to, but this way was just so much simpler.
This link demystified the syntax for OR and AND in conjunction with IF:
http://www.experiglot.com/2006/12/11/how-to-use-nested-if-statements-in-excel-with-and-or-not/
Here are a couple of links on the problem of trying to do an equate comparison on text cells. They too feel that the solution that I went with is a fallback and inelegant, but list it anyhow:
http://superuser.com/questions/284022/comparing-two-strings-in-excel
http://www.mrexcel.com/archive/Formulas/2870.html
New column 3: If the row is an "OFF" with a matching "ON" then print the total elapsed time. If not, print a blank string. This shows how I continue to cling to visual readability in the output. The basic test uses new column 2, comparing it to another preconfigured cell $P$1 that has the word "Matched" entered.
=IF(AND(D3=$O$1,J3=$P$1),I3-I2,"")
New columns 4 and 5: Cell format is time as 39:00:00. First cell of each column contains 0. Checks one of the other identity columns for whether it's an "A" or "B" ON/OFF event. If matched, and it's an "OFF" event, then add the time from new column 3 to the running time total, otherwise just use the previous cell's total.
=IF(AND(C3=$Q$1,D3=$O$1,J3=$P$1),L2+K3,L2)
=IF(AND(C3=$R$1,D3=$O$1,J3=$P$1),M2+K3,M2)
Off to the side, I printed a list of one of all of the different options for one of the identity columns, using this handy tip:
http://www.windowsreference.com/ms-office/how-to-extractlist-unique-values-in-a-column-in-microsoft-excel/
