7 Replies Latest reply on May 15, 2012 10:17 AM by Alex Kerin

# Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

Hello,

Any help on how to calculate the working minutes, which is a time difference between two (02) date, taking into account the Week day (Monday-Friday), bank holidays, and working business hours (9:00 - 18:00)

Date 1: X1, format: DD/MM/YYYY hh:mm:ss

Date 2: X2, format: DD/MM/YYYY hh:mm:ss

List of Bank Holidays: January 1st, May 1st, May 8th...etc

Paul

• ###### 1. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

Have a look at this old thread of Joe Mako's for a couple of ideas (whatever happened to Joe - used to be a real regular?).  Best way may be use James's solution for regular week days and a supplementary table like I suggested for public holidays.

http://www.tableausoftware.com/forum/would-it-be-possible-get-count-weekdays-between-two-dates

• ###### 2. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

Thanks Richard for the reply, appreciated.

I'm not good enough in finding out the formulas, I tried to do it but with no success.

I'm looking for a time difference in minutes. The above link point out to day difference.

I'm attaching my Excel file, if anyone could help.

Thanks

Paul

• ###### 3. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

I'm sure it's doable in Tableau using an approach based on the thread I sent you - but definitely a bit of work and not trivial.  I might try to put some more hints together when I get a minute.

But before I do - just one other thought.  What is your real data source?  Is it Excel, or was that just some sample data?  The reason I ask is that if your data really is coming from Excel, you might find it easier to do this by using Excel formulas to create an extra column in your source data.

• ###### 4. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

The real date source is Excel.

I'll have a look as well how to do it in excel.

• ###### 5. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

Just wanted to check.  How'd yall do on this effort?  I'm embarking on the same effort.

• ###### 6. Re: Calculate Working minutes taking into account Week days, bank holidays, and Business Hours

Hi Richard,

The link goes to the main forums page. I tried to search for it but couldn't find it. Anyway we can get access to it now?

Regards,

Jai