473,287 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Calculate Average Date

Hi all

How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates:
----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates,
but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the
time difference formatted as '? Months ? Days ? Minutes ? Hours ? Seconds'

Thanks for your help,

Kind Regards,
Steve.
Nov 22 '05 #1
28 3995
Cor
Hi Steve,

This is a fine question for one of the languages groups

microsoft.public.dotnet.languages.csharp
and
microsoft.public.dotnet.languages.vb

They eat this stuff in those groups.

Cor
Nov 22 '05 #2
Hi Cor

I thought that this question would be language independant, but if you think
I will get a better/quicker response from these groups I will post it again
there.

Thank you.
Steve.

"Cor" <no*@non.com> wrote in message
news:eY**************@TK2MSFTNGP11.phx.gbl...
Hi Steve,

This is a fine question for one of the languages groups

microsoft.public.dotnet.languages.csharp
and
microsoft.public.dotnet.languages.vb

They eat this stuff in those groups.

Cor

Nov 22 '05 #3
Steve <stevea@centurion-ms_RemoveThis_.co.uk> wrote:
How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates:
----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates,
but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the
time difference formatted as '? Months ? Days ? Minutes ? Hours ? Seconds'


It sounds like you're actually after the average of a bunch of
*timespans*. You create a TimeSpan by subtracting one DateTime from
another, either using the Subtract method or (in C#) using the "-"
operator. (eg TimeSpan diff = end-start; )

You could then (for instance) add the number of ticks in each timespan,
divide by the number of timespans, and then create a new timespan for
the new value.

You can find the min/max in the same way as for anything else, just
using comparisons (either using CompareTo or (in C#) the comparison
operators).

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #4
Hi Jon

Thanks for your quick response. Just to confirm, is the following what you
would recommend?
-----------------------------------------
Dim myTimeSpan As New TimeSpan
Dim myTicks As Long

For row = 0 to rowCount -1 '// Loop through the DataGrid rows and get the
Start/End Dates
myTimeSpan = EndDate.Subtract(StartDate)
myTicks += myTimeSpan.Ticks
Next

myTicks = myTicks/rowCount
Dim myNewTimeSpan As New TimeSpan(myTicks)
Days = myNewTimeSpan.Days
Hours = myNewTimeSpan.Hours
Minutes= myNewTimeSpan.Minutes
Seconds= myNewTimeSpan.Seconds
---------------------------------------

That returns a good figure, just want to check that I have the logic
correct.

Your help is much appreciated, thank you.

Steve.

"Jon Skeet [C# MVP]" <sk***@pobox.com> wrote in message
news:MP************************@msnews.microsoft.c om...
Steve <stevea@centurion-ms_RemoveThis_.co.uk> wrote:
How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates: ----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates, but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the time difference formatted as '? Months ? Days ? Minutes ? Hours ?
Seconds'
It sounds like you're actually after the average of a bunch of
*timespans*. You create a TimeSpan by subtracting one DateTime from
another, either using the Subtract method or (in C#) using the "-"
operator. (eg TimeSpan diff = end-start; )

You could then (for instance) add the number of ticks in each timespan,
divide by the number of timespans, and then create a new timespan for
the new value.

You can find the min/max in the same way as for anything else, just
using comparisons (either using CompareTo or (in C#) the comparison
operators).

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

Nov 22 '05 #5
Steve <stevea@centurion-ms_RemoveThis_.co.uk> wrote:
Thanks for your quick response. Just to confirm, is the following what you
would recommend?
-----------------------------------------
Dim myTimeSpan As New TimeSpan
Dim myTicks As Long

For row = 0 to rowCount -1 '// Loop through the DataGrid rows and get the
Start/End Dates
myTimeSpan = EndDate.Subtract(StartDate)
myTicks += myTimeSpan.Ticks
Next

myTicks = myTicks/rowCount
Dim myNewTimeSpan As New TimeSpan(myTicks)
Days = myNewTimeSpan.Days
Hours = myNewTimeSpan.Hours
Minutes= myNewTimeSpan.Minutes
Seconds= myNewTimeSpan.Seconds
---------------------------------------

That returns a good figure, just want to check that I have the logic
correct.


That looks fine to me, yes.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #6
Steve <stevea@centurion-ms_RemoveThis_.co.uk> wrote:
I thought that this question would be language independant, but if you think
I will get a better/quicker response from these groups I will post it again
there.


No, you were right. The only difference involved here is really whether
or not you can use the various overloaded operators.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #7
Cor
Hi Jon,

In this newsgroups are less programmers active, I know that in the VB
language group are a lot of people who have fun to help with this questions.
You can say I was guessing that it was with the csharp group the same.

Cor
Nov 22 '05 #8
Cor
Hi Steve

The question is language independent but in the language groups are people
who love to do this just as a challenge.

In past was Fergus Cooney active in the vb.language group, he would stay up
all night to get the best solutions for things like this.

That was why I told it.

Cor
Nov 22 '05 #9
Cor <no*@non.com> wrote:
In this newsgroups are less programmers active, I know that in the VB
language group are a lot of people who have fun to help with this questions.
You can say I was guessing that it was with the csharp group the same.


It is - but I don't actually think either of those groups is
particularly suitable, as the question wasn't really about languages. I
would say that here and microsoft.public.dotnet.framework are the best
groups for the question, personally.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #10
Cor
Hi Steve,

I think this is not the good approach

I would make it more like this (I did not real check it but I thinks it
reached it very far)

Module main
Public Sub main()
Dim startdates() As String = _
{"01/01/2004 12:50", "02/01/2004 13:40", "02/01/2004 14:30"}
Dim enddates() As String = _
{"02/01/2004 18:40", "02/01/2004 13:57", "02/01/2004 19:50"}
Dim span(2) As TimeSpan
Dim totalspan As TimeSpan
For i As Integer = 0 To 2
span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))
Dim a As String = totalspan.ToString
Next
span.Sort(span)
MessageBox.Show("min = " & span(0).ToString & vbCrLf & _
"max = " & span(2).ToString & vbCrLf & _
"avarage = " & totalspan.FromSeconds _
(totalspan.TotalSeconds / 3).ToString())
End Sub
End Module

In this way you use the features from the Net framework.

But as I said, I think there are better solutions.

Cor

Thanks for your quick response. Just to confirm, is the following what you
would recommend?
-----------------------------------------
Dim myTimeSpan As New TimeSpan
Dim myTicks As Long

For row = 0 to rowCount -1 '// Loop through the DataGrid rows and get the
Start/End Dates
myTimeSpan = EndDate.Subtract(StartDate)
myTicks += myTimeSpan.Ticks
Next

myTicks = myTicks/rowCount
Dim myNewTimeSpan As New TimeSpan(myTicks)
Days = myNewTimeSpan.Days
Hours = myNewTimeSpan.Hours
Minutes= myNewTimeSpan.Minutes
Seconds= myNewTimeSpan.Seconds
---------------------------------------

That returns a good figure, just want to check that I have the logic
correct.

Nov 22 '05 #11
Cor <no*@non.com> wrote:
I think this is not the good approach

I would make it more like this (I did not real check it but I thinks it
reached it very far)

Module main
Public Sub main()
Dim startdates() As String = _
{"01/01/2004 12:50", "02/01/2004 13:40", "02/01/2004 14:30"}
Dim enddates() As String = _
{"02/01/2004 18:40", "02/01/2004 13:57", "02/01/2004 19:50"}
Dim span(2) As TimeSpan
Dim totalspan As TimeSpan
For i As Integer = 0 To 2
span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))
Dim a As String = totalspan.ToString
Next
span.Sort(span)
MessageBox.Show("min = " & span(0).ToString & vbCrLf & _
"max = " & span(2).ToString & vbCrLf & _
"avarage = " & totalspan.FromSeconds _
(totalspan.TotalSeconds / 3).ToString())
End Sub
End Module

In this way you use the features from the Net framework.


Why is that better than the version the OP posted? It gives the min and
the max, but requires the whole list to be sorted (which is
unnecessary) in order to do so. It also does more conversions for no
reasons, as far as I can see.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #12

Convert Dates to Ticks
Average & Mean the Ticks
Convert it back to date.

done:)

"Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi all

How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates: ----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates, but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the
time difference formatted as '? Months ? Days ? Minutes ? Hours ? Seconds'

Thanks for your help,

Kind Regards,
Steve.

Nov 22 '05 #13
Cor
Hi Jon,
Why is that better than the version the OP posted? It gives the min and
the max, but requires the whole list to be sorted (which is
unnecessary) in order to do so. It also does more conversions for no
reasons, as far as I can see.


Give a shorter sample in whatever language.

But complete running with the start as the OP told, than you are a man and
show that you know it better. Now I do not tell for what this sounds for me.

It are only 14 lines of code inlcluding the module name, (there is one test
line stayed in it).

If you do not show it I expect that you cannot.

Cor
Nov 22 '05 #14
Difference between two dates is easy, if they are DateTime objects:

TimeSpan t = dtFinish - dtStart;
Debug.WriteLine(t);

How's that grab you?

--Tim

"Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi all

How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates: ----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates, but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the
time difference formatted as '? Months ? Days ? Minutes ? Hours ? Seconds'

Thanks for your help,

Kind Regards,
Steve.

Nov 22 '05 #15
Cor <no*@non.com> wrote:
Why is that better than the version the OP posted? It gives the min and
the max, but requires the whole list to be sorted (which is
unnecessary) in order to do so. It also does more conversions for no
reasons, as far as I can see.

Give a shorter sample in whatever language.


Steve's example already *was* shorter. However, shorter is not
necessarily better anyway.
But complete running with the start as the OP told, than you are a man and
show that you know it better. Now I do not tell for what this sounds for me. It are only 14 lines of code inlcluding the module name, (there is one test
line stayed in it).


(Actually, the code you posted was 20 lines, but that's not important.)

Your code included parsing each date/time, when there was no indication
that that was actually needed. Your version also included a sort for no
reason, which made the complexity of the function the complexity of the
sort, which is likely to be O(n log n), instead of O(n) which is all
that's needed.

The one benefit your way has (which is indeed a benefit) is that
there's no need to actually take the ticks each time - just sum the
timespans and get the ticks at the end. (Using ticks is a good idea
though, rather than seconds, as the accuracy will be better. For
instance, your method would not get a good average when all the times
are in the sub-second range...)

Here is a more general version in C#:

static void CompareDifferences (IList starts, IList ends,
out TimeSpan avg, out TimeSpan min,
out TimeSpan max)
{
min = TimeSpan.MaxValue;
max = TimeSpan.MinValue;

TimeSpan sum = new TimeSpan();

for (int i=0; i < starts.Count; i++)
{
TimeSpan diff = (DateTime)ends[i] - (DateTime)starts[i];
sum += diff;
if (diff < min)
min=diff;
if (diff > max)
max=diff;
}
avg = new TimeSpan (sum.Ticks/starts.Count);
}

To me, that's more readable code. It's general (taking lists of
starts/ends, returning the three timespans as out parameters - that
could be done in a different way if required, of course) and it also
performs well.

In production code I'd also do argument verification, of course.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #16
Cor
Hi Jon,

That code you supplied did not show anything on my screen when I pasted it
in my C# IDE as a blank C# project. (To be true I did not because I knew
that before)

Your long part of code should gives the same as the little piece here (I
could yours not test because it is incomplete) And strange enough you use
the same amount of casting, while you said there was no need for it. (And I
could use milliseconds in the span, but I did not, because the date was
supplied in minutes, so seconds would me more than precise)

\\\
public function Avaragetime (byval startdate() as string, byval enddate() as
array) as string
dim totalspan as timespan
dim span(2) as timespan
For i As Integer = 0 To 2
span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))
Next
return totalspan.FromSeconds (totalspan.TotalSeconds / 3).ToString())
end function
////
Here is a more general version in C#:

static void CompareDifferences (IList starts, IList ends,
out TimeSpan avg, out TimeSpan min,
out TimeSpan max)
{
min = TimeSpan.MaxValue;
max = TimeSpan.MinValue;

TimeSpan sum = new TimeSpan();

for (int i=0; i < starts.Count; i++)
{
TimeSpan diff = (DateTime)ends[i] - (DateTime)starts[i];
sum += diff;
if (diff < min)
min=diff;
if (diff > max)
max=diff;
}
avg = new TimeSpan (sum.Ticks/starts.Count);
}

Nov 22 '05 #17
Cor <no*@non.com> wrote:
That code you supplied did not show anything on my screen when I pasted it
in my C# IDE as a blank C# project. (To be true I did not because I knew
that before)

Your long part of code should gives the same as the little piece here (I
could yours not test because it is incomplete)
The only *conversion* my version does is casting (which would be an
unboxing operation). Yours does parsing, even though the OP never
mentioned the originals being in a string form. No matter though.
And strange enough you use
the same amount of casting, while you said there was no need for it. (And I
could use milliseconds in the span, but I did not, because the date was
supplied in minutes, so seconds would me more than precise)

\\\
public function Avaragetime (byval startdate() as string, byval enddate() as
array) as string
dim totalspan as timespan
dim span(2) as timespan
For i As Integer = 0 To 2
span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))
Next
return totalspan.FromSeconds (totalspan.TotalSeconds / 3).ToString())
end function
////


No, that then doesn't do what my version does - it doesn't work out the
maximum and minimum. It also still hasn't been fixed to actually use
the length of the arrays in question - it blindly assumes that there
are 3 elements.

I'm still wondering why on earth you thought Steve's earlier code "is
not the good approach"... what did you have against it, exactly? The
only real difference is that it builds up the number of ticks instead
of as a timespan.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #18
Cor
Hi Jon,

I'm still wondering why on earth you thought Steve's earlier code "is
not the good approach"... what did you have against it, exactly? The
only real difference is that it builds up the number of ticks instead
of as a timespan.

Exactly and the timespan is made for it.
I do not use anymore a multiple add to multiply also.

I even forgot this, it can even be brought down till this beneath
(I also included your sentence about the fixed array, although I had the
idea, that you should know that that is not important for this sample)
\\\
public function Avaragetime (byval startdate() as string, byval enddate() as
array) as string
dim totalspan as timespan
For i As Integer = 0 To startdate.lenght - 1
totalspan =CDate(enddates(i)).Subtract(CDate(startdates(i)))
Next
return totalspan.FromSeconds (totalspan.TotalSeconds /
startdate.lenght).ToString())
end function
///
Although in a real situation I probably would use a datatable if it was a
databound operation or else an arraylist or a self build from Ilist derived
collection, that hold items, object, properties whatever in that situation:
startdate, enddate, timespan (the last to be able to sort).

And the string was given in the sample from Steve.

Cor
Nov 22 '05 #19
Cor <no*@non.com> wrote:
I'm still wondering why on earth you thought Steve's earlier code "is
not the good approach"... what did you have against it, exactly? The
only real difference is that it builds up the number of ticks instead
of as a timespan.
Exactly and the timespan is made for it.
Either way seems fine to me, to be honest.
I do not use anymore a multiple add to multiply also.
Not sure what you mean by this...
I even forgot this, it can even be brought down till this beneath
(I also included your sentence about the fixed array, although I had the
idea, that you should know that that is not important for this sample)
\\\
public function Avaragetime (byval startdate() as string, byval enddate() as
array) as string
dim totalspan as timespan
For i As Integer = 0 To startdate.lenght - 1
totalspan =CDate(enddates(i)).Subtract(CDate(startdates(i)))
Next
return totalspan.FromSeconds (totalspan.TotalSeconds /
startdate.lenght).ToString())
end function
///
Did you test this? It doesn't seem to make much sense to me, as you're
no longer adding anything - effectively you end up with the total span
being *exactly* the last span.

Of course, it's still less readable than my version as far as I'm
concerned. I'm worried that you seem to think that code which is
shorter is necessarily better. Here's a version of my method which does
everything including finding the min and max, to the finest accuracy
possible (with an integral number of ticks), efficiently, but in only 6
lines of code. Amazing! ... but totally unreadable:

static void CompareDifferences (IList starts, IList ends,
out TimeSpan avg, out TimeSpan min, out TimeSpan max) {
min=TimeSpan.MaxValue;max=TimeSpan.MinValue;TimeSp an s=new
TimeSpan(); for (int i=0;i<starts.Count;i++){TimeSpan
d=(DateTime)ends[i]-(DateTime)starts[i];s+=d;if(d<min)min=d;
if(d>max)max=d;}avg=new TimeSpan (s.Ticks/starts.Count);}
Although in a real situation I probably would use a datatable if it was a
databound operation or else an arraylist or a self build from Ilist derived
collection, that hold items, object, properties whatever in that situation:
startdate, enddate, timespan (the last to be able to sort).

And the string was given in the sample from Steve.


Well of course Steve gave examples in text, as there's no other way of
posting to the newsgroup - but his example code showed that he'll be
getting his dates from a DataGrid, so there was no need to do any
string parsing.

The main objection I had to your original code was that it was very
inefficient *and* it changed the input data, both due to sorting the
array - when sorting the array is (IMO) a far less natural way of
finding the min and the max values than just checking on each
iteration.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #20
Cor
Hi Jon,

I did not test this last part, and you are right with this one, but I hope
you saw that it had to be.
totalspan +=CDate(enddates(i)).Subtract(CDate(startdates(i)) )


Did you test this? It doesn't seem to make much sense to me, as you're
no longer adding anything - effectively you end up with the total span
being *exactly* the last span.


About readability, that is a matter of taster and that can be a discussion
from here to the moon.

The sort was very easy and without the disadvantage I tell you now very
usefull, however you cannot get with this methode the index from the
original array and therefore you should build your own Ilist collection.
That I find a much more disadvantage than what you said, but I do not
mention that in a discussion of course.

Cor


Nov 22 '05 #21
Cor <no*@non.com> wrote:
I did not test this last part, and you are right with this one, but I hope
you saw that it had to be.
totalspan +=CDate(enddates(i)).Subtract(CDate(startdates(i)) )

Yup - but possibly if the code had been more readable in the first
place you wouldn't have made the error? Maybe if you'd been more
concerned with correctness than brevity?
Did you test this? It doesn't seem to make much sense to me, as you're
no longer adding anything - effectively you end up with the total span
being *exactly* the last span.
About readability, that is a matter of taster and that can be a discussion
from here to the moon.


Yes, but I hope you would at least agree that my 6 line version was
less readable than the longer version, and that your idea that shorter
is necessarily better (as exhibited in your challenge to find a shorter
piece of code than yours as a sort of oneupmanship test) is therefore
just plain silly.
The sort was very easy and without the disadvantage I tell you now very
usefull, however you cannot get with this methode the index from the
original array and therefore you should build your own Ilist collection.
That I find a much more disadvantage than what you said, but I do not
mention that in a discussion of course.


So you would advocate copying all the data and then performing a sort,
which is expensive in both time *and* space, rather than the more
obvious (to my mind) method of just going through all the data and
finding the min/max? What possible advantage makes up for the
performance disadvantages?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #22
Hi Everyone

I really appreciate all the help that I am receiving with regards to ths
issue, as far as I am concerned the issue is resolved with the example that
I posted based on advice from Jon. The method I use may not be the desired
choice by others, but I understand it and it completes processsing approx
6000 records in my DataGrid in under a second. It won't be run that often
and if it is then it will be only once at a time, but it was important that
I accomplished it.

Thanks again for your help.

Regards,
Steve.

"Steve" <stevea@centurion-ms_RemoveThis_.co.uk> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
Hi all

How would I find out the average date when given a bunch of dates? For
example, I want to find the average length in time from the following dates: ----------------------------------------------------
Start Date End Date
01/01/2004 12:50pm 02/01/2004 18:40pm
02/01/2004 13:40pm 02/01/2004 13:57pm
02/01/2004 14:30pm 02/01/2004 19:50pm

Min = 17 Minutes (2nd entry)
Max = 1 Day 5 Hours 10 Minutes (1st entry)
Average = ??
-----------------------------------------------------
Also, I have a function that calculates the time difference between 2 dates, but it doesn't work all that well if you take int account months, does
anyone have a good function that can take 2 date parameters and return the
time difference formatted as '? Months ? Days ? Minutes ? Hours ? Seconds'

Thanks for your help,

Kind Regards,
Steve.

Nov 22 '05 #23
Cor
Jon,

Do you know what is very annoying that you did not make C# code from Steve
his sample but from my Sample.

This is from Steve

Start/End Dates
myTimeSpan = EndDate.Subtract(StartDate)
myTicks += myTimeSpan.Ticks
Next

You did translate it to this in the same way as in my sample from that time

{TimeSpan diff = (DateTime)ends[i] - (DateTime)starts[i];
sum += diff;}

span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))

And not in the way Steve did.
And that was exactly why I told Steve that there was a better way.

Cor
Nov 22 '05 #24
Cor <no*@non.com> wrote:
Do you know what is very annoying that you did not make C# code from Steve
his sample but from my Sample.
Sorry, could you rephrase that? I didn't understand it.
This is from Steve

Start/End Dates
myTimeSpan = EndDate.Subtract(StartDate)
myTicks += myTimeSpan.Ticks
Next

You did translate it to this in the same way as in my sample from that time
I didn't "translate" it at all - I wrote what I'd have written in my
own code.
{TimeSpan diff = (DateTime)ends[i] - (DateTime)starts[i];
sum += diff;}

span(i) = CDate(enddates(i)).Subtract(CDate(startdates(i)))
totalspan = totalspan.Add(span(i))

And not in the way Steve did.
And that was exactly why I told Steve that there was a better way.


.... except you confused things by adding a nasty (IMO) way of finding
the min and the max. If you'd just said "You could actually just add
the timespans together rather than the ticks" then I wouldn't have
disagreed with you at all - indeed I even agreed with that back in an
earlier post:

<quote>
The one benefit your way has (which is indeed a benefit) is that
there's no need to actually take the ticks each time - just sum the
timespans and get the ticks at the end.
</quote>

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #25
Cor
That was the only reason from my example as I said before and why I told it
was better.
<quote>
The one benefit your way has (which is indeed a benefit) is that
there's no need to actually take the ticks each time - just sum the
timespans and get the ticks at the end.
</quote>

Nov 22 '05 #26
Cor
But succeeded in what you always are doing , an OP has taken your wrong
advice again.

Nov 22 '05 #27
Cor <no*@non.com> wrote:
But succeeded in what you always are doing,
What exactly do you mean by that? If you wish to make this a personal
slanging match, I suggest you email me instead.
an OP has taken your wrong advice again.


It's not wrong. It'll work perfectly well. We've improved the code
since then, yes - and the OP may take note of that - but there was
nothing actually *wrong* with the original code.

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too
Nov 22 '05 #28
Cor
Hi Jon,

You always give me the idea that when I become in a tread with you, you see
me as someone who knows nothing about it.

I had a little bit sorrow when I saw that mail from Steve in the same time
that you agreed that there was a better way, but the difference is not that
important, that I agree.

So forget it,

Cor
Nov 22 '05 #29

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve | last post by:
Hi all How would I find out the average date when given a bunch of dates? For example, I want to find the average length in time from the following dates:...
1
by: Alex | last post by:
I have a comma separated text file in the form: 11/28/2004 12:00:00, 2.78655354 11/28/2004 12:00:10, 1.9978 11/28/2004 12:00:20, 0.978 11/29/2004 12:00:00, 1.98467 11/29/2004 12:00:10, 3.005 ...
1
by: HEMH6 | last post by:
Weihted Average Write a C program to calculate and print the weighted average of a list of N floating point number, using the formula Xave = F1X1 + F2X2+...+ FnXn where the F's are...
4
by: gaga | last post by:
hi guys, a part of my program requires me to calculate an average of items that are sold. the easiest way to do that would be writing a function, but im having trouble making up the parameters. if...
1
by: Sedigh | last post by:
Hi Everybody, I need to write a macro on my Excel sheet to calculate the average of cells for me. This is the code I have written but the average function is not working. Can you please let me...
3
by: paeh | last post by:
hello..can anyone help me. I am beginner in programming. I need to make a system that can calculate moving average. my system process will be executed according to certain schedule such as daily,...
7
by: jamesnkk | last post by:
Hi, Although this question may not directly link to Access, but I think it a common question to those developer.so hope you could suggest a solution. How do I get the average cost for Item sold,...
0
by: SuzK | last post by:
I am trying to calculate in VBA in Access 2002 a moving average and update a table with the calculations. Fields in my WeeklyData table are Week Ending (date) ItemNbr (double) Sales Dollars...
1
by: Michel | last post by:
Hello, I need to calculate moving averages of weekly data during the last year. After some search, I believe that the best approach will be to get a dataset from the SQL Server database, browse...
12
by: denveromlp | last post by:
Hello, I'm new to Access and trying to calculate a rolling 12 month average from some time data. Each data point is a date and a measurement taken at that date. As far as I can tell, the only...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.