Converting a number(Hours) into Time format hh:mm:ss
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.
Hi Pranav, dates are numbers, hh:mm:ss is only a format to see them but internally they are just numbers, with 'day' as a base unit, ie: '01/01/2018 12:00:00'-'01/01/2018 00:00:00'=0.5 (half day)
Have you tried without multiplying wekends/holydays by 24? Can you say an example of values used, the expected result and the result returned?
