Converting days into HH:MM:SS

[ekta22]

Hi,

I am trying to get the number of days between two dates by using the datediff function. I get the total number of seconds. But then I was to display the total number of seconds in HH:MM:SS format. I have tried it many ways but can't get it to work. My formula below keeps rounding it to just hrs. So my result looks something like this 1200:00:00. I don't see the minutes and seconds. Here is what I have so far. Would appreciate any help.

Dim d1, d2 
Dim d3 as Number

d1 = CDate({N1.START_DATE})

d2 = CDate({N1.END_DATE})

d3 = DateDiff("s",d1, d2)



Dim nOutageMins as Number 'as Variance
Dim nOutageSecs as Number 'as Variance
Dim nOutageHour as Number 'as Variance
Dim nTotalMins  as Number 'as Variance
dim nSecs as Number

nSecs = d3

nTotalMins = ( nSecs \ 60  )
      nOutageSecs = Remainder ( nSecs , 60  )
      nOutageHour = nTotalMins  \  60 
      
      nOutageMins = Remainder (nTotalMins, 60)
      formula =  CStr( nOutageHour, "00" ) + ":" + CStr ( nOutageMins, "00") + ":" +  CStr ( nOutageSecs,"00" )

Thanks!

 

[ekta22]

I just saw a solution under FAQs. Will try that out first. If I have any proob with that will post again.

Thanks!

 

[ekta22]

I get the same results. It keeps rounding it to just hours. minutes and seconds is always 00.

Any ideas why this is happening?

Thanks!

 

[lbass]

The problem is that you are starting out using cdate instead of cdatetime. The cdate is eliminating the time portion.

-LB

 

[IanWaterman]

Try

nTotalMins = int( nSecs \ 60 )
nOutageSecs = Remainder ( nSecs , 60 )
nOutageMins = Remainder ( nTotalMins , 60 )
nOutageHour = int( nSecs \ 3600 )

formula = CStr( nOutageHour, "00" ) + ":" + CStr ( nOutageMins, "00") + ":" + CStr ( nOutageSecs,"00" )

Ian

 

[ekta22]

Thanks lb! datetime did it..