How to convert a number(Hours) into Time format 'hh:mm:ss'

[pranaview]

Hi Guys,

Problem : I need to calculate time difference between two successive date in a column(It is a DateTime field i.e. 'MM/DD/YYYY hh:mm:ss') excluding weekends/holidays.

So, I have got the difference between the two successive dates and calculated the number of weekends/holidays in between as well. Now I was thinking the next part should be pretty easy as I will just multiply the number of weekends/holidays by 24 and subtract it from the original difference that I have got but for some reason I am not getting the desired result. I have tried Time/Time# functions but it does not seem to work. I know if I can convert the (number of weekends * 24) value into Time format(hh:mm:ss) , I can easily subtract it from the original difference. I am kinda stuck here so any help will be very useful at this moment.

Thanks,

Pranav

[tresesco]

Could you clarify (possibly with an example)- what you have now (after some calculations) and exactly what you want out of it ?

[pranaview]

Hi Tresesco,
So, I am working on a dashboard for showing Time taken for processing each request.
For ex: If I take one RequestId, it will have multiple records with different status and Datetime logged against each record(status), let's say this field is called Date1. What I want is to show how much time it took for people to change the request from one status to another. Hence the need to calculate the difference between successive dates in the field Date1. Now while calculating subsequent time difference, I need to take weekends/holidays into consideration. So I was using NetworkDays() for this requirement.

I am getting Successive time difference using the below line of code
Interval(Date1-Previous(Date1),'hh:mm:ss')
which gives me a result something like this - 32:12:14

Now this is the first part. Now I have to take number of weekends/holidays into consideration. Suppose it's x. I will convert days into hours i.e. 24x
Now I need to deduct this 24x value from 32:12:14 for the final output.

Hope this gives you a better idea on what I require.
Thanks for you response.
Pranav

[tresesco · Accepted solution]

I guess you could simply subtract your number of weekends/holidays (x) from the difference like :

Interval(Date1-Previous(Date1) -x,'hh:mm:ss')

and that should work.

[pranaview]

Makes sense. Times like these make you think "Why didn't i think of that?". Anyway thanks a lot for your help and you're right that this should work.

[pranaview]

Tresesco, just out of curiosity, if suppose I had to convert that value into Time format i.e. 'hh:mm:ss'. How would I do that?