Converting seconds to hh:mm:ss format

Hi

I am converting seconds to hh:mm:ss from seconds (CR2016)

I usually use the formula below which works fine for anything under 24 hours

CStr(CTime(currentfieldvalue/ 86400), "hh:mm:ss")

I have a few fields that go above 24 hours, so for 25:00:06 - this reads 01:00:06.

I have tried

 CStr(CTime(currentfieldvalue/ 3600), "hh:mm:ss") - doesnt work

Any ideas on how this can be used for anything over 24 hours

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?
 
Rgonzo1971Commented:
then try
CStr(Trunc( currentfieldvalue / 3600), 0) &  Right(CStr(CTime(currentfieldvalue / 86400), "hh:mm:ss") , 6)

Open in new window

0
 
ste5anSenior DeveloperCommented:
Something like

CStr(Trunc( currentfieldvalue / 86400)) & "d " &  CStr(CTime((currentfieldvalue mod 86400) / 86400), "hh:mm:ss") 

Open in new window


should do it. Cause any time formatting is formatting time, not durations.
0
 
Rgonzo1971Commented:
HI,

pls try
CStr(Trunc( currentfieldvalue / 3600)) & ":" & CStr(CTime(currentfieldvalue / 86400), "mm:ss") 

Open in new window

Regards
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Mark WilsonBI DeveloperAuthor Commented:
Hi
Thanks for the answers but not quite correct

Answer 1

CStr(Trunc( currentfieldvalue / 86400)) & "d " &  CStr(CTime((currentfieldvalue mod 86400) / 86400), "hh:mm:ss")  this gives it in days, hh,mm,ss - I would like the hours in hh:mm:ss - so 25:00:06 - not 01:01:00:06

Answer 2

CStr(Trunc( currentfieldvalue / 3600)) & ":" & CStr(CTime(currentfieldvalue / 86400), "mm:ss")

This is nearly there but gives additional numbers i.e. 25:00:00:06
0
 
Rgonzo1971Commented:
then try
CStr(Trunc( currentfieldvalue / 3600)) &  Right(CStr(CTime(currentfieldvalue / 86400), "mm:ss") , 6)

Open in new window

0
 
Mark WilsonBI DeveloperAuthor Commented:
Thanks for the answer

This is now coming out as 25.0000:06
0
 
Mark WilsonBI DeveloperAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.