Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Time Difference Formula

Status
Not open for further replies.

michelin

Technical User
Dec 18, 2011
38
US
I have following data:
Time A Time B
10:25:00 10:30:00
10:26:00 10:27:00

I need to know the time difference between the soonest time from column A and the soonest time from column B (which would be 2 minutes). What formula would you advice to use? I'm using Crystal Reports 2008 Version 12.
 
Your example AND requirement do not make sense!

You are loking for the EARLIEST value in each column in your table independently. Senseless!

Please explain the business case for this requirement.
 
Not much to go on here, and as Skip says, your requirement does not seem to make any sense.

Be that as it may, the following code would work for the sample data provided. Place the formula in the Report Footer.

Code:
Datediff("n",Minimum({Table.Time_A}),Minimum({Table.Time_B}))

Cheers
Pete

 
Thank you for your questions! So, I have 2 units that are responding to a call.
Unit Time Assigned Time AtScene
A 10:25:00 10:30:00
B 10:26:00 10:27:00

I need to know, what is the difference between time first unit assigned and time first unit at scene (I need to know how long does it take to respond to a call- how long did the patient have to wait for a help). In this case the difference would be 2 minutes. Does that make a sense? I don't care that it took unit B 1 minute to respond, because a patient had to wait 2 minutes- and that is the difference I'm looking for.
 
Write a formula as Charliy suggested:
@TimeDiff
datediff("n",{Assigned Time },{AtScene})

Then Insert --> Summary
Choose the field to summarize
[tt]TimeDiff[/tt]
Calculate this summary
[tt]Minimum[/tt]
Summary Location
[tt]Group Footer[/tt] (or Report Footer- depending on your report)

Hope this helps.
 
There are several things that trouble me about this scenario

1) You only have TIMES. What if the incident was close to midnight?
[pre]
Unit Time Assigned Time AtScene

A 23:59:00 00:04:00
B 00:01:00 00:03:00
[/pre]
SOLUTION: You ALSO need date or Date.Time

2) What about other incidents and other Units? Surely you do not have a table for each incident? Surely there is ONE TABLE for ALL Unites and Incidents, YES? Well any solution would need to accommodate all the variables, YES?
 
Thank you Betty, I will try it!

Skip, thank you for your response. I really appreciate it!
1.the format is actually date.time, so it contains a date also (i'm just showing times, i'm sorry if it made my example confusing).
2.correct. I have one table. My report is grouped by incident and i'm researching each incident separately. For example:

Incident I
Unit Time Assigned Time AtScene
A 00:05:00 00:06:00
B 00:01:00 00:10:00

Incident II
Unit Time Assigned Time AtScene
A 00:10:00 00:14:00
B 00:20:00 00:23:00

I think I know how to continue and made the calculations based on each incident separately (response time for Incident I- 5 minutes, for Incident II- 4 minutes). But I'm trying to figure it out the first step- the difference between time first unit assigned and time first unit at scene.
 
Please disregard my earlier post. That gives the unit that took the least time between Assigned and Atscene time.

Group on Incident

Formula
@WaitTime
datediff("n",Minimum ({Assigned Time }, {Incident}),Minimum ({AtScene}, {Incident}))

Place @WaitTime in Group Footer (Group Header) of Incident.



 
BettyJ... THANK YOU VERY MUCH!!!!! that worked like a charm :) You made my day!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top