Calculating Elapsed Days

[BJ]

Hi,

I have data like this
08/03/05 14:35:30.0
08/03/05 14:38:24.0
08/03/05 14:41:38.5
08/03/05 14:45:46.0
08/03/05 14:47:19.5
08/03/05 14:48:21.0
08/03/05 14:49:14.5
08/03/05 14:49:15.0
08/03/05 14:50:13.0
08/03/05 14:50:13.5
08/03/05 14:51:43.0
08/03/05 14:53:12.5
08/03/05 14:55:15.5
08/03/05 14:57:08.0
08/03/05 15:00:54.5
08/04/05 08:38:34.5
08/04/05 15:23:13.5
08/04/05 15:24:37.0
08/04/05 15:24:59.0
08/04/05 15:24:59.5
08/04/05 15:25:15.0

What I want to do is, calculate th eelsasped days depending on the date
and time
i.e.
08/04/05 15:25:15.0 = 31( for Jan) + 28( for Feb ) + 31 ( for Mar) + 8
+ 15.25/24 ( convert the time to date) = 98.6

Anyone knows, how to do it in Access Query ?

Thanks in Advance
BJ

 

[John Spencer]

Well, you need to convert the data to a date time type.

The formula below will do that. Although, since these dates are not in
standard US format mm/dd/yy , I'm not sure how CDate will handle the
conversion.

CDate(Left([TheField], 17))

So the first thing is to test this conversion. If it works then you can use
the result and subtract two date time.

The following should work for that.

CDbl(CDate(Left([TheField], 17)) - DateSerial(5,1,1))

For a discussion about handling dates see
International Dates in Access at:
http://allenbrowne.com/ser-36.html

 

[BJ]

I have found a way to convert to a US date format.

I just dont know how to calculate the days..
Will try your method.

Thanks a lot for your help.

BJ

 

[BJ]

Hi John,

The code you have given is somehow not working.

Select CDbl(CDate(Left(Temp_Load_Tab.F1,17)) - DateSerial(5,1,1)) As
ElaspDays from Temp_Load_Tab;

Also, I need to interpret the time like if I have a date like

08/04/05 15:25:15 = 31( for Jan) + 28( for Feb ) + 31 ( for Mar) + 8
+ 15.25/24 ( convert the time to date) = 98.6

This is not achievable in your code.

Thanks
BJ

 

[John Spencer]

This (using US date format - for April 8 2005)
CDbl(CDate(Left("04/08/05 15:25:15.1",17)) - DateSerial(2004,12,31))
Returns this
98.6425347222248
However, I might point out that you are counting April 8th as a full day and
then adding in the hours for April 8. So I think you might want 97.6 as the
result. That formula would be

CDbl(CDate(Left("04/08/05 15:25:15.1",17)) - DateSerial(2005,1,1))

If you want to round the time portion down to one decimal you can use a
rounding function to do so.

 

[BJ]

Hey John,

This works absolutely fine.
Thanks a lot for your help.

Thanks
BJ