PDA

View Full Version : Sleeper: How to divide time series in to shorter time series in vba



backspace20
03-15-2016, 05:51 AM
I have time series which is in m/d/yyyy h:mm format. Like this:

1/1/1983 5:20
1/1/1983 5:21
1/1/1983 5:25
1/1/1983 8:00
1/1/1983 8:00
1/1/1983 8:03
1/1/1983 8:04
1/1/1983 8:10
1/1/1983 8:15
1/1/1983 8:20
1/1/1983 8:40
1/5/1983 11:30
1/5/1983 11:40
1/5/1983 11:50

As it can be seen the time series is divided in to 4 parts : data between 5 to 6 o'clock , between 8 to 9, 9 to 10 and finally 11 to 12 fifth of Jan.

what the code would be if i want to say that if difference between data is less than (for example 1 hour) consider them as a group, like this :

1/1/1983 5:20 1
1/1/1983 5:21 1
1/1/1983 5:25 1
1/1/1983 8:00 NA
1/1/1983 9:00 2
1/1/1983 9:03 2
1/1/1983 9:04 2
1/1/1983 9:10 2
1/1/1983 9:15 2
1/1/1983 9:20 2
1/1/1983 9:40 2
1/5/1983 11:30 3
1/5/1983 11:40 3
1/5/1983 11:50 3

I have to mention that I do not want to consider groups with one value as a group for example 1/1/1983 8:00 is not a group.
Cheers,

SamT
03-15-2016, 08:32 AM
Date/Times in Excel are stored as Doubles, (unless entered into a cell as Text.) The Integer part of the number is the number of days from a specific date, usually 1/1/1904, and the decimal pert is the time ad a decimal fraction of 1 day or 24 hours.

5AM is stored as =5/24 so >=5/24 is 5AM or later and <.25 is earlier than 6AM.

Note that 5:30:15AM = 5/24 + 30/24*60 + 15/24*60*60