Converting Decimal Time to nn:ss 2

[jvet4]

I am using a query to get the time duration by subtracting two times.  What is the correct way to convert the decimal answer to nn:ss?

 

[kathryn]

There is a format option in the query grid column property box.&nbsp;&nbsp;Have you played with that? <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jvet4]

Huh, I did not even realize you could do that.&nbsp;&nbsp;That worked perfectly.&nbsp;&nbsp;<br><br>Thanks a lot for the help!!!!<br><br>Jason

 

[jvet4]

I just found a slight problem with subtracting two times (hh:nn:ss-hh:nn:ss = nn:ss).&nbsp;&nbsp;Using this method I get the correct answer for all times except then the first time has rolled over past 00:00:00&nbsp;&nbsp;For these I am getting a time that it 24 - the difference (00:02:30 - 23:52:57 = 23:50:27 not 00:09:33).&nbsp;&nbsp;I have gone through all my data types and no where do I have times associated with dates.&nbsp;&nbsp;<br><br>Any Ideas??

 

[kathryn]

Just as long as you are POSITIVE that the roll over will never be more than one day, you can use an If...then statement in your query.&nbsp;&nbsp;Of course, for some reason, an If in Access is spelled Iif!!?&nbsp;&nbsp;I have referred to your two fields as fldstart and fldend.<br><br>YourCalculationFieldName:&nbsp;&nbsp;Iif(fldstart&gt;fldend,(fldend-fldstart+24:00:00),fldend-fldstart)<br><br>I am not sure about the syntax to add the 24 hours.&nbsp;&nbsp;You might have to use the DateAdd function.&nbsp;&nbsp;Maybe something like this<br><br>Iif(fldstart&gt;fldend,DateAdd(&quot;h&quot;,24,fldend)-fldstart,fldend-fldstart)<br><br>Hope this helps.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jvet4]

<br>The If...Then statement worked.&nbsp;&nbsp;To add the 24 hours it was just end-start+24.&nbsp;&nbsp;<br><br>Thank You much for the help!!!<br><br>Jason

 

[MichaelRed]

jvet4,<br><br>It is generally better to include the date portion in any date time calculations.&nbsp;&nbsp;If the source variables are declared as Date/Time in MS Access, you are 'using' the storage anyway, so might as well get the info as well as the storage.&nbsp;&nbsp;<b>IF</b> you do this, then you can use the DateDiff function and get the difference to be correct, however the DateDiff Function will only return the number of intervals, so you need to generate the &quot;time&quot; format from the interval.<br><br>Michael<br>&quot;There is never time to do it right but there is always time to do it over&quot;<br>