Aoristic Analysis (time and dates)

[rjc_29]

I am trying to set up a spreadsheet and eventually a macro that wil
work out probable times of the day when an event occurred i.e. aoristi
analysis- based on two date fields and two time fields. For example:
Datefrom: 01/04/04
Timefrom: 13:00
Dateto: 03/04/04
Timeto: 21:05

refer to link below.
.http://www.crimereduction.gov.uk/toolkits/fa020404.htm

A tool has been developed for a GIS application but i would like t
know how this could be done in excel especially when there may b
several hundred records with times/date

 

[DNF Karran]

This is something I have never done before so i am going to have to si
down and work it out for my peace of mind.

I would suggest creating a user defined function that returns a 2
element array containing the probabilities. The problem I'm having t
work on now is the fact things fall over when over one day i
required.

Duncan

Option Explicit

Function TimeProbability(dbStartTime As Double, dbEndTime As Double) A
Variant

Dim arTimeValues(23) As Single
Dim siProbability As Single
Dim i As Integer

siProbability = 1 / (Hour(dbEndTime - dbStartTime) + 1)

For i = Hour(dbStartTime) To Hour(dbEndTime)
arTimeValues(i) = siProbability
Next

ArrayTest = arTimeValues

End Functio

 

[DNF Karran]

I think i've got it:

1st set up a spreadsheet with the following columns:

Start Time - End Time - Prob. Sum - 01:00 - 02:00 - 03:00 - etc to
00:00

Format the start time & end time columns to dd/mm/yyyy hh:mm (o
similar) to enable the input of combined date/time figures.

Put the prob. sum as "=SUM(D2:AA2)" for error checking

Select the 24 time columns on the row you want to inout values for an
insert the UDF I have written which takes the inputs StartTime an
EndTime. THis will then need to be array entered (ctrl+shift+ente
instead of just entered)- et voilla

The only issue you may encouter is if you have hundreds and hundreds o
these to calculate at once, you might want to go make a cup of te
while xl thinks.

Here's the code for the UDF though I have also attached a workboo
incase I lost you!

Duncan

Option Explicit


Function TimeProbability(dbStartTime As Double, dbEndTime As Double) A
Variant

Dim arTimeValues(23) As Single
Dim siProbability As Single
Dim siTimeDiff As Single
Dim inDays As Integer

Dim i As Integer, j As Integer

siTimeDiff = dbEndTime - dbStartTime

Select Case siTimeDiff

Case Is < 1

siProbability = 1 / (Hour(siTimeDiff) + 1)

If Application.WorksheetFunction.RoundDown(dbStartTime, 0)
Application.WorksheetFunction.RoundDown(dbEndTime, 0) Then
For i = Hour(dbStartTime) To Hour(dbEndTime)
arTimeValues(i) = siProbability
Next
Else
For i = Hour(dbStartTime) To 23
arTimeValues(i) = siProbability
Next
For i = 0 To Hour(dbEndTime)
arTimeValues(i) = siProbability
Next
End If

Case Is = 1
siProbability = (1 / 24)

For i = 0 To 23
arTimeValues(i) = siProbability
Next

Case Is > 1
inDays = Application.WorksheetFunction.RoundDown(dbEndTime, 0
- Application.WorksheetFunction.RoundDown(dbStartTime, 0)

siProbability = 1
((Application.WorksheetFunction.RoundDown(dbEndTime - dbStartTime, 0)
24) _
+ (Hour(dbEndTime) - Hour(dbStartTime) + 1))

For i = Hour(dbStartTime) To 23
arTimeValues(i) = arTimeValues(i) + siProbability
Next
For i = 0 To Hour(dbEndTime)
arTimeValues(i) = arTimeValues(i) + siProbability
Next

If Hour(dbEndTime) >= Hour(dbStartTime) Then
For j = 1 To inDays - 1
For i = 0 To 23
arTimeValues(i) = arTimeValues(i) + siProbability
Next i
Next j

Else
For j = 1 To inDays - 2
For i = 0 To 23
arTimeValues(i) = arTimeValues(i) + siProbability
Next i
Next j
End If

End Select

TimeProbability = arTimeValues

End Functio

Attachment filename: timeanalysis.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54940

 

[rjc_29]

thanks for that but is there a way that the four columns are utilise
i.e date from, time from, date to, time to in four seperate columns
And then generate a chart automatically from the results?


Surfing the web earlier and found the following link.

However given that needs to be installed on c drive and using offic
2000 i cant get it to work especially at work due to restriction
placed on our computers. Is there a way round this in anyway?

http://www.jratcliffe.net/ware/aoristicexcel.htm

Have also attached an example of the charts on a spreadsheet. Chart
are embedded pictures.

Any ideas

Attachment filename: example.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55017

 

[rjc_29]

soory to ask so many questions after you spent time doing this (sorr
about the pun!) but haven't used UDF's only run macros through tool
on menu bar. Can't get it to work have attached another example wit
three additional sample records ideally i would prefer the date/time t
work in seperate columns rather than having to combine them in on
column as creates additional problems and time consuming also.

Is it possible to have the forumula written as a macro which at a clic
of a button i can run it on my spreadsheet of temporal data?

i need the macro to work just on the cells containing info in th
particular columns, because there is not a typical range as this i
dependent upon the number of records in the work sheet

Attachment filename: example2.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55022

 

[DNF Karran]

First thing I have to say is- OOPS!

There was an error relating to the probability calculation in the cod
on my last post. I have corrected and will add the amended code below.

I have also added some code that will input the UDF as you requir
though does not currently add the "sum" below each column.

One thing I have noticed is one of the charts you have sent me does
"day" summary. This will require another UDF to calculate th
probability by day- though should be easier than the time one!

I'll have a look during today and get the file set up for you.

Duncan

* Error in previous code was in the "Case <1" range. Here's the ne
code:*

Case Is < 1

If Day(dbStartTime) = Day(dbEndTime) Then

siProbability = 1 / (Hour(dbEndTime) - Hour(dbStartTime)
1)

For i = Hour(dbStartTime) To Hour(dbEndTime)
arTimeValues(i) = siProbability
Next

Else

siProbability = 1 / ((24 - Hour(dbStartTime))
Hour(dbEndTime) + 1)

For i = Hour(dbStartTime) To 23
arTimeValues(i) = siProbability
Next
For i = 0 To Hour(dbEndTime)
arTimeValues(i) = siProbability
Next

End If

*And to auto input: *

Option Explicit

Sub InputTimeArray()

Dim lRow As Long

For lRow = 2 To Range("a2").End(xlDown).row
ActiveSheet.Range("G" & lRow & ":AD" & lRow).FormulaArray = _
"=TimeProbability((B" & lRow & "+C" & lRow & "),(D" & lRo
& _
"+E" & lRow & "))"
Next

End Su

 

[DNF Karran]

Oh, and if your datefrom and timefrom columns are date and tim
formatted, you can just add them together to imput them to the UDF.

Dunca

 

[DNF Karran]

After spending some time being a bit more structured in my approach w
have the following for weekdays. It's simpler than the time code as
spent 30mins drawing pictures to work out what would be the bes
approach at the start!!

I haven't created a auto input for this though you should be able t
work it out from the last posts. Anyway, I'll be sitting down an
tidying up the time function this afternoon. I'll post the file whe
I've done in case you want it.

Duncan

Function DayAnalysis(siStartDay As Single, siEndDay As Single) A
Variant
Dim arDayValues(6) As Single
Dim siProbability As Single
Dim loFirstWeekIndex As Long
Dim loEndWeekIndex As Long
Dim loWeeksInvolved As Long
Dim i As Integer, j As Integer

If siStartDay > siEndDay Then DayAnalysis = "INPUT ERR": Exi
Function

loFirstWeekIndex = siStartDay - Weekday(siStartDay, vbMonday) + 1
loEndWeekIndex = siEndDay + (7 - Weekday(siEndDay, vbMonday))

loWeeksInvolved = (loEndWeekIndex - loFirstWeekIndex) / 7

siProbability = 1 / (siEndDay - siStartDay + 1)

If loWeeksInvolved > 2 Then
For j = 3 To loWeeksInvolved
For i = 0 To 6
arDayValues(i) = arDayValues(i) + siProbability
Next i
Next j

For i = (Weekday(siStartDay, vbMonday) - 1) To 6
arDayValues(i) = arDayValues(i) + siProbability
Next

For i = 0 To Weekday(siEndDay, vbMonday) - 1
arDayValues(i) = arDayValues(i) + siProbability
Next

ElseIf loWeeksInvolved = 2 Then

For i = (Weekday(siStartDay, vbMonday) - 1) To 6
arDayValues(i) = arDayValues(i) + siProbability
Next

For i = 0 To Weekday(siEndDay, vbMonday) - 1
arDayValues(i) = arDayValues(i) + siProbability
Next

ElseIf loWeeksInvolved = 1 Then

For i = (Weekday(siStartDay, vbMonday) - 1) T
(Weekday(siEndDay, vbMonday) - 1)
arDayValues(i) = arDayValues(i) + siProbability
Next

End If

DayAnalysis = arDayValues

End Functio

 

[rjc_29]

Duncan

Thanks for all your hard work. Where do you copy all this code to?
Having a thick day today - do you put in the macro part?

How would i add thedatefrom and timefrom columns together - personall
would want to keep them seperate as easy unless this can b
automated!!

Spreadsheet version would be helpful to see how you've done it i.e.
working copy.

Thanks agai

 

[DNF Karran]

I have attached the "final" version of the file with the updated tim
analysis function.

Before using this file I would reccommend *rigorous* testing as I hav
never set up a function to run this kind of analysis before. If an
issues are thrown up then please let me know!

On the subject of "adding the date and time columns", the function
have been given inputs that are B2+C2 and D2+E2 as the date forms a
integral part of the calculation. This means you can use the fil
format you sent me (which has been used to set up the payments).

Be aware of the following constraints-

The array the calculation returns starts at 00:00 and ends at 23:00
This cannot be changed easily as the assumption 00:00 is where a da
starts and 23:59 is where it ends is an integral part of th
calculations. You should find this to be correct though as the colum
labelled "00:00" contains a probability for 00:00 to 01:00, the star
of a new day.

Due to the type of variables used, accuracy is limited to 5 decima
places. I have not changed this as if you are working with 100+ entrie
then the effect becomes insignificant.

Dunca

Attachment filename: aoristic- final.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55195

 

[rjc_29]

Have tried it out but not sure how the macro works do i have to selec
the whole worksheet or do i have to drag the rows down?

Have attached a copy of sample date/times. Also ran the same time
using a GIS function which uses aoristic analysis and these are th
results it produced from the records i have attached;

Time of Day:
0000-0059 11.5095
0100-0159 11.1762
0200-0259 6.59929
0300-0359 6.26595
0400-0459 4.26595
0500-0559 6.26595
0600-0659 5.15484
0700-0759 2.89793
0800-0859 4.80953
0900-0959 2.45248
1000-1059 4.1338
1100-1159 5.07498
1200-1259 6.53646
1300-1359 4.10544
1400-1459 6.06353
1500-1559 11.998
1600-1659 7.17009
1700-1759 8.4063
1800-1859 6.59101
1900-1959 10.2734
2000-2059 11.1177
2100-2159 18.9765
2200-2259 11.0972
2300-2359 9.21502

Day of week:
Monday 39.2375
Tuesday 5.54342
Wednesday 6.71444
Thursday 6.43572
Friday 19.0607
Saturday 43.9208
Sunday 60.0875

Any ideas

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=55204

 

[DNF Karran]

Yep- I made too many assumptions about something I don't know muc
about. I have assigned each time to a single 1 hour range and it look
like that has thrown my probability out, possibly the weighting too.

Basically I'm just concerned they may have weighted the apportionmen
of probability by minutes not hours then grouped it as hours.

My method is more simple but has led too slight errors thoug
cumulativley I am out by 1% to 20%.

What values should I return for these:

13/5/2004 17:00 14/5/2004 8:00
13/5/2004 17:30 14/5/2004 8:20
14/5/2004 21:45 14/5/2004 22:15

Next:

The weekday function also appears to be a complete mess- Oh ******s
the input macro was wrong. It needs changing to:

Private Sub InputDayArray(lrow As Long)
ActiveSheet.Range("AE" & lrow & ":AK" & lrow).FormulaArray =
"=DayAnalysis((B" & lrow & "),(D" & lrow & "))"
End Sub

The day function is now matching perfectly to the GIS. (Yippee!!)

I shall play at the weekend with the day function.

The InputArray macro will input the formulas for you in any row with
value in col A. It won't do the formatting though. I'll set it up mor
fully if I have time at the weekend and look at developing this into
full application.

Dunca

Attachment filename: test.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=55207

 

[rjc_29]

Duncan

May also be worth noting that using the GIS version have to chang
times to integers i.e 17:45 would be 1,745 etc

here are what it produces for the your 3 time/dates:
0000-0059 0.129167
0100-0159 0.129167
0200-0259 0.129167
0300-0359 0.129167
0400-0459 0.129167
0500-0559 0.129167
0600-0659 0.129167
0700-0759 0.129167
0800-0859 0.0625
0900-0959 0
1000-1059 0
1100-1159 0
1200-1259 0
1300-1359 0
1400-1459 0
1500-1559 0
1600-1659 0
1700-1759 0.129167
1800-1859 0.129167
1900-1959 0.129167
2000-2059 0.129167
2100-2159 0.629167
2200-2259 0.629167
2300-2359 0.129167

I won't be able to test against the GIS at weekend as not at work.
Reason need this in excel as easier to use etc, and don't always hav
access to the GIS tool.

Thanks for all your help

 

[DNF Karran]

Got it-

The GIS system is reading the first one :

13/5/2004 17:00 14/5/2004 08:00 1.00000
13/5/2004 17:30 14/5/2004 08:20 1.00000

to be a probability of:

0.0666
0.0625

and doesn't count the first one as being within 08:00 to 08:59

while i read them both as 0.0625 and get it as an end time of 08:00
being in the range 08:00 to 08:59.

You probably know better than me on this one- who is right? (OK
probably GIS but I have to ask!)

Duncan

 

[rjc_29]

not sure i understand what you mean

 

[DNF Karran]

OK a simpler example-

Start Time 08:00
End Time 09:00

GIS would give probability as:
08:00 ; 1
09:00 ; 0

I get the probability to be:
08:00 ; 0.5
09:00 ; 0.5

GIS has put 09:00 in the range 08:00 to 08:59

I have put 09:00 in the range 09:00 to 09:59

From what I can see GIS is working in hours but is dealing with th
situation differently to me. The following time should give 0.5 & 0.
if I am right:

14/5/2004 08:20 14/5/2004 09:20

And this one should give 1 but should be 0.5 & 0.5 (as in my system)

14/5/2004 08:59 14/5/2004 09:00

Now, if I am right the question is where should 09:00 be?

Is 09:00 part of 08:00 to 08:59 or is it part of 09:00 to 09:59?

And are we both wrong and we should be working in minutes an
apportioning the probability according to how many minutes are in a
hour?

The results are differring by 20%+ so I think the best thing is to ge
a concrete and detailed description of how this type of analysis shoul
be done. I'll have a word with a mate who works in GIS for the NHS an
see what he can tell me but it'll be monday before I know.

Dunca

 

[DNF Karran]

I have spoken to my mate and he is unsure as to what you need- he's more
into geographical data.

All I can say is there are 3 possible systems- GIS, my UDF and a
potential upgrade to my UDF that works with minutes. If you want to
e-mail me (this thread is getting a bit OTT) then see my website:

uk.geocities.com/duncankarran

Duncan

 

[rjc_29]

Agree has become OTT and have have emailed you via website