Time Formatting 1

[kermitforney]

I need to be able to format the result of a time calculation.

Target TimeSpent Time Diff (in minutes)
200 75 125

I would like to display the TimeDiff as a time, so it is calculated as follows: 125/1440 formatted as a time = 02:05

I would like to format the result as 2hrs 5mins, how is this possible? I have seen dome formatting via control source, but have no experience myself.

Any help is greatly appreciated!!

TGIF D

 

[PHV]

=Format(([Target]-[TimeSpent])/1440, "h""hrs"" nn""mins""")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kermitforney]

PHV, to the rescuuuuuue . . again!!!

How did you become so dang knowledgeable PHV?? Now the only remaining question is, "How can resources be managed dynamically when using Database Mirroring in SQL Server 2008"?

Any insight PHV?? D TGIF!!!

 

[kermitforney]

Damn, I spoke too soon. Is there anyway to wrap the format in an If, Then or vice-versa??

=IIf(IsNull(Sum([lngTargetTime])/60),0,Sum([lngTargetTime])/60)
[\code]

 

[PHV]

You may try the Nz function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kermitforney]

Couldn't get the syntazx to work correctly, so I tried the If/Then conditional again.

Got it!!

=Format(IIf(IsNull(Sum([lngTargetTime])/1440),0,Sum([lngTargetTime])/1440),"h""hr"" nn""min""")

 

[kermitforney]

There is one problem that has snuck it's little head into my solution!!

If the total is above 24hours, then the total resets.

Ex. If the result is 25:16 or 25hr 16mins, then what is displayed shows 1:16 or 1hr 16mins.

Anyway around this?

 

[PHV]

=IIf(IsNull(Sum([lngTargetTime])),"",(Sum([lngTargetTime])\60) & "hrs " & (Sum([lngTargetTime]) Mod 60) & "mns")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kermitforney]

Thanks, again PHV!!