Finding Median between two dates

[sbehrens]

Hi, newbie here,

My question is this:

I have a query in access with two columns of dates

Example:

1WKBegin (5-9) 1WKEnd

and I am trying to find median for them, I tried the following formulas;

Exp1: [1WKEnd] - [1WKBegin (5-9)] / 2
Exp1: ([1WKEnd] - [1WKBegin (5-9)]) / 2

I got a date but it is the wrong date. Can anyone help me on this?
Thxs

 

[jackd]

Median: (from Wikipedia)
a median is described as the numerical value separating the higher half of a sample, a population, or a probability distribution, from the lower half. The median of a finite list of numbers can be found by arranging all the observations from lowest value to highest value and picking the middle one. If there is an even number of observations, then there is no single middle value; the median is then usually defined to be the mean of the two middle values

 

[sbehrens]

Ok, so I am using the wrong word here, forgive me my sin, it is 5:40pm and I'm still at work on a Friday night trying to figure this out

Is there anyway I can find the middle between two dates or close to it, if it is even number?

That was assignment that was given to me by the boss lady. Is there any hope for me or do I start crying now.

 

[james_lankford]

maybe
datediff the number of days between the two dates
http://www.techonthenet.com/access/functions/date/datediff.php

then take the number of days and divide by 2

then dateadd that number of days to the start date
http://www.techonthenet.com/access/functions/date/dateadd.php

 

[xenou]

Haven't tried it out but maybe:

Exp1: [1WKBegin (5-9)] + Round(([1WKEnd] - [1WKBegin (5-9)] / 2), 0)

(i.e., add the average of the days to the the first day. Rounding will keep it to whole days.).

 

[sbehrens]

Thank you for answering my question everyone. I've been working on this all week

I tried different variations of the formula for the DateDiff but I keep getting a number rather than a date, this is formula;

Exp1: DateDiff('d',[OPDay],[1WkEnd])

I also, tried the other formula;

Exp1: [1WKBegin (5-9)] + Round(([1WKEnd] - [1WKBegin (5-9)] / 2), 0)

with no luck either

any suggestion

Thanks

 

[james_lankford]

I tried different variations of the formula for the DateDiff but I keep getting a number rather than a date,

you're supposed to get a number
it gives you the number of days between dates

so do datediff on jan 1 2011 and dec 31 2011 and it would give 365 (or 364)
now take 1/2 of that
1/2 of 365 is 183 more or less
now do dateadd jan 1 2011 and 183 and that will give you the date that's 1/2 way in between

look at the links I posted
go to the links, scroll down and look at the examples