Problem matching date/time between worksheets

[hazgibson]

Hi,

I'm afraid this is a rather long-winded description of my problem but I can't think of any way of making it shorter and yet clear!

I handle several large time-series datasets. These consist of two parts. The first part is data which is recorded automatically by a data-logger at 15 minute intervals. This dataset is uninterrupted from the start - ie there is a record for every single 15-minute interval since the start of the dataset.

The data is collected from the data-logger in a .csv format which can be directly imported into Excel. The logger uses an odd time reference format with a number 1-365 in one column for day of the year, and a military-time style number in another column for time (115 for 1:15am, 2045 for 20:45, etc).

The second part is data describing manually-collected samples at intervals ranging from 8 hours to 24 hours. This is entered into separate worksheets with the date and time of the sample in one column as dd/mm/yyyy hh:mm , and then the data items in subsequent columns. The sample times have all been rounded to the nearest 15 minutes to (in theory!) match a logged data item.

I need to be able to readily match up this intermittent sample data with the 15-minute logged data to produce a worksheet which contains all the logged data and then also the sample data in those rows where a sample time matches the logged time.

I am having several problems with this and would appreciate any suggestions. My current method is this:

1) Import the logger data into Excel. Manually generate the date/time field in dd/mm/yyyy hh:mm format by typing in, for instance

21/07/2004 16:00
21/07/2004 16:15

and then using autofill.

2) Import this data file into an Access database. The dataset consists of well over 65536 rows so I cannot handle the entire set in Excel. Furthermore I intended to use Access to match the two datasets by relating the date/time fields.

3) Enter the sample data manually into another Excel file with a date/time field in the same format.

4) Import this into another Access table

5) Relate the date/time field of the two datasets to each other in the database with a type 2 join: All records of logged data, and records of sample data with a matching date/time field.

6) Query the database to extract a portion of the time-series that I need to work on, which can then be exported to Excel for further analysis with the sample times and logged times in matching rows.

Unfortunately at step 6, although some samples are placed alongside the correct row of logged data, most do not appear. I get a complete set of the logged data which contains at best a few of the sample data.

The problem appears to be that Access is unable to recognise values in the date/time fields of the two datasets as being equal and therefore the query does not show records from the sample data as they are not recognised as matching anything in the logged data field.

Although the problem I am having actually occurs in Access I think it is most likely due to my doing something wrong with the date/time values in Excel, hence posting to this board!

I'd really appreciate ANY suggestions - either as to what I might be doing wrong with this method, or of a completely different (and probably easier!) way of achieving what I'm trying to do!

Many thanks,

Harry Gibson

 

[Andrew Poulsom]

I suspect it's a rounding issue.

What formula are you using to round the sample times to the nearest 15 minutes? When formatted as General the 2 times you quoted appear as:

21/07/04 16:00 38189.6666666667
21/07/04 16:15 38189.6770833333

so your rounding needs to be to 10 places of decimals.

 

[hazgibson]

Yes, I also think it's probably to do with rounding but I think it is to do with rounding in the logged data timestamp. I was slightly misleading before - I initially enter the sample times in rounded format:

Say a particular week's samples were at 8 hour intervals, for instance 16:05, 0:05, 08:05 each day. I would simply enter that into the spreadsheet as 16:00, 0:00, 08:00.

The rounding that I do occurs with the logged data times and I think this is likely to be where the problem is. The way I have been generating the logged data timestamp in dd/mm/yyyy hh:mm format (I skipped over this before) is this:

The logger's day number is in column A. I add 37986 to this in column B (for dates in 2004). In column C I enter times 0:00, 0:15, through to 23:45. I then copy this (not fill) to repeat the 0:00 - 23:45 sequence for as many rows as necessary. In the next column I add the contents of columns B and C, but rounded _down_ to 5 places eg =ROUND(B2+C2,5)

It seems fairly laborious and long-winded but the reason I got to doing it this way was in fact to avoid rounding errors! I found that if I either autofilled column C rather than copying, or didn't round in column D, then after a few hundred rows obvious errors started to come in eg a time would come out as 16:29 rather 16:30.

I thought this was due to rounding errors with recurring numbers such as occur at times for 08:00 and 16:00 and so rounded down to 5 places as this was enough to uniquely distinguish each minute and second, but would stop errors from recurring decimals. So have I got confused here - is this going the wrong way?

In any case I have tried rounding the sample times (after entering manually as above) down to 5 places as well as I thought this would definitely make them the same, if both sets were rounded down to 5 places. It didn't help and that was when I gave up and came here!

Sorry i didn't mention all that about my rounding method before - i thought my post was long enough!

Harry

 

[Andrew Poulsom]

If you are rounding both the logged dates/times and the sample dates/times to the same places of decimals, I can't see why it shouldn't work.

I think the only way to find out is to pick one day from each of your tables, bring them into Excel formatted as General and compare them.

 

[hazgibson]

You were absolutely right, thanks! It was only due to a rounding problem and it's all working now.

The problem was happening when I imported new data files into Access, appending them onto the existing tables. In this situation Access had decided that it would un-round the rounded data I was importing - so 38100.66667 as imported became 38100.6666666667 or whatever in the database (and in an Excel file exported back out again). My work-around for this was to import new data to a new table, then copy that table's data to the end of the existing one. Tedious but it works. (Why it happens and the implications of Access changing the data it imports are presumably another story!)

So it is an Access rounding problem, not an Excel one! Sorry about that all you nice Excel people and thanks for your time!

Harry