Calculate TAT between 2 dates and show only working hours in specific format

**allienzaddicts** · 02-26-2014, 07:54 AM

Hello experts,

I'm a noob here and need help in this

I'm actually working on a tracker that has a cell to calculate the turnaround time (tat) which only shows the hours spent within the staff working hours.

Given 2 dates:
A2: Start date = 1/12/12 10:00
B2: End date = 1/16/12 15:00

Shift start time: 8:00 AM
Shift end time: 5:00 PM

Using this formula:
=IF(DAY(B2)-DAY(A2)=0,MIN("17:00",(B2-INT(B2)))-MAX("08:00",(A2-INT(A2))),(MIN("17:00",(B2-INT(B2)))-"08:00")+("17:00"-MAX("08:00",(A2-INT(A2))))+IF(DAY(B2)-DAY(A2)>1,(DAY(B2)-DAY(A2)-1)*(9/24),0))

Result:
In "[h]:mm" format = 41:00
I need the result to be in this format "x day(s) xx hour(s) and xx min" but when I format it, gave me "1 day(s) 17 hour(s) and 00 min".

As you can see, in normal calculation of shift start at 8AM, stop at 5PM. the time difference is 9 hours. And the result above should return "2 day(s) 08 hour(s) and 00 min".

Any idea on how to fix this? And these results should also able to be sum up or average out in my report. Any help is greatly appreciated.

Thank you.

P/S: Would be nice if the formula can only calculate 7.5 working hours and exclude weekends and holidays from the calculation

**Bernie Deitrick** · 02-26-2014, 04:04 PM

Try creating a range of cells named "Holidays" of your holiday dates. Then use this formula in cell C2, which will use 7.5 hours for each full day, exclude weekends, and any holidays listed to give the time in hours between A2 and B2:

Formula:

Please Login or Register  to view this content.

Then in cell D2, use another formula to give the elapsed time in the desired format.

Formula:

Please Login or Register  to view this content.

Note that your actual TAT given the nine hours should have been 7 + 9 + 9 + 7 = 32, and not 41. And note that you could have a TAT of longer than 1 day if the entries are at the same time on consecutive days.

**allienzaddicts** · 03-01-2014, 11:40 AM

Thank you so much for your speedy reply Bernie Deitrick.

I've created the holiday list as per advised.
But I'm really trying to understand how's the formula calculate;
According to the sample dates given, and taking 7.5 hours for 1 full working day.
And between these 2 dates - no holidays

A2: Start date = 1/12/12 10:00 = Thursday
B2: End date = 1/16/12 15:00 = Monday

So based on these dates, Thursday (7 hrs) + Friday (7.5 hrs) + Monday (7 hrs) = 21.5 hrs. Converting this hours to number of days, isn't it supposed to show "2 days, 6.5 hours"?
Using your formula, I'm getting result = 4 days, 2 hours

Sorry if I'm asking very silly question here. But I really want to learn the formula

**daddylonglegs** · 03-01-2014, 12:04 PM

If you count a full day as 7.5 hours isn't it inconsistent to count 10:00 - 17:00 (which is 2 hours short of a full day) as 7 hours? How many hours would you count for 09:00 to 17:00 or 8:30 to 17:00?

Are you accounting for a 1.5 hour lunch or other breaks?

Can your start or end times/dates be outside working hours, e.g. start at 06:00, end at 22:00 or on weekend days?....or will all start/end times/dates be within working hours?

**allienzaddicts** · 03-01-2014, 01:24 PM

Oh yes, I'm actually accounting 1 hour for lunch and 30 mins for other breaks. (With that, full day would be 9 hours).
Thinking of deducting this 1.5 hours later. And yes, start/end dates/times can be outside of working hours but calculation should only show TAT within working hours. The extra hours spent will be calculated separately to see difference of within and outside of working hours spent.

I have actually tried to come up with the tracker on my own recently. Hopefully the attached file would help to understand things that I need to achieve here.

In the attached file,

I have 2 TAT in cell C3 and J3 respectively which I have trouble converting it to "x hour(s), xx mins"
I've used different formula to calculate the TAT, but sometimes it doesn't show the actual working hours.

Formula used: =(NETWORKDAYS(A3,B3,holidays)-1)*(M10-M9)+IF(NETWORKDAYS(B3,B3),MEDIAN(MOD(B3,1),M10,M9),M10)-MEDIAN(NETWORKDAYS(A3,A3)*MOD(A3,1),M10,M9)

**daddylonglegs** · 03-01-2014, 03:10 PM

Originally Posted by allienzaddicts

=(NETWORKDAYS(A3,B3,holidays)-1)*(M10-M9)+IF(NETWORKDAYS(B3,B3),MEDIAN(MOD(B3,1),M10,M9),M10)-MEDIAN(NETWORKDAYS(A3,A3)*MOD(A3,1),M10,M9)

This formula works fine (it's one of mine

) - but you need to change the format to get the correct hours. Custom format C3 as [h]:mm and you'll get 45:00

If A3 or B3 could be on a holiday date then you also need to include your holiday range in the 2nd and 3rd NETWORKDAYS functions

**Bernie Deitrick** · 03-01-2014, 06:10 PM

sorry, should have subtracted 2 instead of just 1

Formula:

Please Login or Register  to view this content.

**allienzaddicts** · 03-02-2014, 01:20 AM

Yes, it is one of your formula

. I'll give it a shot and see what's the results gonna be.

**allienzaddicts** · 03-02-2014, 01:21 AM

Originally Posted by Bernie Deitrick

sorry, should have subtracted 2 instead of just 1

Formula:

Please Login or Register  to view this content.

Thanks Bernie, testing both of your formula now

**allienzaddicts** · 03-02-2014, 01:37 AM

Originally Posted by allienzaddicts

Thanks Bernie, testing both of your formula now

As I'm working with 9 hours calculation now. I've changed the formula and it works perfectly now

Thanks Bernie

**allienzaddicts** · 03-02-2014, 01:41 AM

Originally Posted by daddylonglegs

This formula works fine (it's one of mine

) - but you need to change the format to get the correct hours. Custom format C3 as [h]:mm and you'll get 45:00

If A3 or B3 could be on a holiday date then you also need to include your holiday range in the 2nd and 3rd NETWORKDAYS functions

Thanks daddylonglegs - formula works well. Nice one!

Cheers~
*Going to break all the formula 1 by 1 and learn each function used in the formula.

**Amarjeet111** · 05-16-2018, 07:59 AM

Hi would you please help me to calculate tat where no break and no weekend to be exclude.

Please share with an example

**FDibbins** · 05-16-2018, 11:03 PM

Amarjeet111, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

Calculate TAT between 2 dates and show only working hours in specific format

LinkBack

Thread Tools

Rate This Thread

Display

Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Re: Calculate TAT between 2 dates and show only working hours in specific format

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Calculate response time in working hours between two dates

Formula to calculate working hours between two dates not working correctly?

Calculate working hours between two dates

calculate the number of working hours between two dates

calculate the amount of time between 2 dates (working hours)

Bookmarks

Bookmarks

Posting Permissions