Date / time spacing

Bebbspoke

Board Regular
Joined
Oct 10, 2014
Messages
193
Hi - I have numerous historic data files - all are date & time stamped in a single column. All data is at five minute intervals over a ten week period. The data is in contiguous rows - there are no gaps; but sporadically the data is not contiguous - individual or strings of time stamps are absent, and different files may have different gaps!
Please - is there a method whereby Excel can sort the data such that blank rows are inserted for the missing time stamps? - such would enable me to align all the files for rapid comparison. Thank you.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Try this:

In a blank sheet use a formula to add 5 minutes over a ten week period.
The use a VLOOKUP to bring the data in.
Copy/Paste Special Values and delete the original sheet.
 
Upvote 0
U can also sought the data, just add the 5 min over a ten week period and then sought, this will sought the data leaving the blanks cells at the last, then can autofilter the same and can use vlookup.
 
Upvote 0
Do all files have the same 5 minutes gap?

E.g. are they all 12:05, 12:10, 12:15

or can they be as follows: 12:01, 12:06, 12:11

Personally I would use a database query but some VBA can be written for you to do what you ask.
 
Upvote 0
vmjan02 - ... the data is already sorted in date & time sequence - the data is as a continuous stream - there are NO blanks. I regret I have no idea what vlookup is.
 
Upvote 0
Hi Comfy - all date/time stamps have the SAME 5 minute gap , as in your first example

You could do as Special-K99 suggests and use vlookups.

If you could let us know the purpose of your comparison we might be able to suggest an alternative method.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top