Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing problem in calculating business seconds in terms of business days. I know using Networkdays we can get the number of business days but I am not sure how can I calculate total business seconds between any 2 given dates. For e.g. Date1: 1/1/2017 9:30 AM and Date2: 1/5/2017 10:30 PM. The business seconds between these two dates will be 340200.
Please help!
Thanks!
Akash
How you are calculating the 3,40,200 seconds. 1st is sunday and then 2, 3, 4, 5 are the Business working days. Here, Each business working day how many hours you are considering as Business work day. Can you explain little more. I assume if each day we are working as 9 Hrs the seconds should be in 158400. I am not figure it out how you are getting 340200 ??/
Hi Akash,
Can you define how did you get 340200 seconds ?
Since 1/1/2017 is Sunday, I am excluding the number of seconds in that day starting at 9:30 AM. So for Sunday it's 14hrs 30minutes of time (52200) we have to exclude. Rest other 3 complete days (3*86400) and one day with 22 hrs 30 minutes (81000) we have to add up which is equal to 340200. Does this makes sense? This is basically calculating total number of seconds between the two days timestamp and excluding non working days seconds from it. I need such calculating to figure out the turnaround time of an activity in business seconds.
Since 1/1/2017 is Sunday, I am excluding the number of seconds in that day starting at 9:30 AM. So for Sunday it's 14hrs 30minutes of time (52200) we have to exclude. Rest other 3 complete days (3*86400) and one day with 22 hrs 30 minutes (81000) we have to add up which is equal to 340200. Does this makes sense? This is basically calculating total number of seconds between the two days timestamp and excluding non working days seconds from it. I need such calculating to figure out the turnaround time of an activity in business seconds.
You can use below calculation , I have converted your dates into number, you can replace number with your start and end date fields
=Interval(42740.9375 - if(WeekDay(42736.40)=6,floor(42736.40+1),if(WeekDay(42736.40)=5 ,floor(42736.40+2))),'ss')
This logic helps. But what if I have to incorporate company holidays. How do I do that in an interval function?