Date/time calculation 1

[bacalaoyalcapurria]

Hello,

I need to do a date/time calcutation with two fields. Find the latency between the two dates and time or just the time. In other words, how long it took the Problem to get validated. Then, I need to get an average.
I have to fields
1. Received
The information in here looks like, "4/3/2006 10:00:38 AM"


2. Validated
The information in here looks like, "Problem Validated, Current Time: 4/5/2006 8:01:03 PM"

I tried doing this
Validated: Right([Alerts].[ValidateStatus],20)
to get rid of the text and keep the date and time, but it's not working. Well, it does to some extent. Is there a way to tell Access get rid of the text and leave me with the date and time?
Am I doing the right thing to accomplish this calculation?
Do I need the date or can I just do it with the time?


Thanks,

Rita

 

[dhookom]

If the two fields are Date/time fields, there is no text stored in the field, only the date/time. To get the difference, use the DateDiff() function to return the number of seconds, minutes, hours, days, or whatever:
For minutes:
=DateDiff("n",[Received], [Validated])
For hours:
=DateDiff("n",[Received], [Validated])/60

If the fields are text fields, you can convert them using CDate([Received]).


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bacalaoyalcapurria]

Thanks,

Is there a better way to extract the date and time from this string; "Problem Validated, Current Time: 4/5/2006 8:01:03 PM"

I did this Validated: Right([Alerts].[ValidateStatus],20)
but it's not working too well.
I just need the date and time

 

[bacalaoyalcapurria]

I got it using the mid function. Thank you

 

[dhookom]

I had already suggested the better method of converting your text to a date/time in my previous post. Using a string function such as Mid() or Left() is not always accurate or efficient.

If you only want the date portion of the text field, you can use the DateValue() function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I had thought the Validated field only contained the date/time value. I would still use something like:

CDate(Mid([Validated],Instr([Validated],":")+1) )

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bacalaoyalcapurria]

The dates and time are differents. I can't use the DateValue() Function. I have over 500 records and the data is different.
An example;

Incident Validated, Current Time: 4/3/2006 9:01:01 PM
Incident Validated, Current Time: 4/4/2006 12:11:05 PM
Incident Validated, Current Time: 4/4/2006 9:19:05 AM
Incident Validated, Current Time: 4/4/2006 8:45:01 AM
etc......

I want to extract just the date and time like

4/3/2006 9:01:01 PM
4/4/2006 12:11:05 PM
4/4/2006 9:19:05 AM
4/4/2006 8:45:01 AM

To accomplish it, I did this on the Query Section.
Validated:
CDate(Mid([Alerts].[ValidateStatus],34))

and it worked.

Now, I'm in the process of finding out the latency between these two fields. I need to get an answer in seconds. Can this be done using the date and time like I have it or do I need to separate them and do it individually?

Received Validated
4/3/2006 4:50:48 PM 4/3/2006 4:55:06 PM
4/3/2006 10:11:44 AM 4/3/2006 11:01:01 PM
4/4/2006 6:04:09 PM 4/4/2006 6:09:06 PM


Thanks,

Rita

 

[PHV]

As already suggested by Duane, use the DateDiff function:
Latency: DateDiff('s', [Received], [Validated])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Since Validated is a larger text field, you may need to use something like:
Latency: DateDiff('n', [Received], CDate(Mid([Validated],Instr([Validated],":")+1) ) ) /60
to get the hours.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bacalaoyalcapurria]

I'm very close in getting my result. Right now I'm getting seconds as the result.

Received Validated
4/3/2006 8:56:59 PM 4/3/2006 8:57:07 PM
4/3/2006 8:12:25 PM 4/3/2006 8:13:01 PM
4/3/2006 8:48:52 AM 4/4/2006 7:01:02 AM

Latency
8
36
79930

Why will not Access let me do this;
Latency: DateDiff('h:n:s:',[Receivedd],[Validated])

so I can get my desired results as

0:00:08
0:00:36
22:12:10

I tried doing this,
Latency: DateDiff('n', [Received], CDate(Mid([Validated],Instr([Validated],":")+1) ) ) /60

but I get results like ,

-931439.98333
-931439.98333
-931441.78333
-931429.1

I exported the data to Excell and there I can get my results. Excell will convert the seconds to 0:00:08.
The reason I want to do it in Access is so can perform averages. It'll be a lot easier if I do it in Access.

Thanks,

Rita

 

[dhookom]

When I try this in the Immediate window:
? dateDiff("n",#4/3/2006 8:56:59 PM#,#4/3/2006 8:57:07 PM#)
I get a value of 1.

Does your Validated field contain only the date/time value or is it something like you stated earlier:

"Problem Validated, Current Time: 4/5/2006 8:01:03 PM"

Your last reply suggests it is only the date/time value.

Do you realize you can simply subtract one time value from another to get the difference in days units.
Format(#4/3/2006 8:57:07 PM#-#4/3/2006 8:56:59 PM#,"0.0000000") = 0.0000926 days

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

You may try this:
Latency: Format([Validated]-[Received], 'hh:nn:ss')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886