Hour Calcuation

[Guest]

Dear all,

I need to calculated the TAT (Turn Around Time):

We receive particular task, which is needed to be completed in 06 working
hours. otherwise we need to give reasons for the delay. To find out
manually it takes lot of time for each task. That is the reasons i need this
formula for calculating the hours in TAT.

For Example:
1. A task is given on 21.04.2007 03:30:00 PM (Which is a Working day, Friday)
2. Saturday, 22.04.2007 is a holiday.
3. Sunday, 23.04.2007 is a weekly holiday.
4. We have replied with the details about the task on 24.04.2007 11:00:00 AM
5. 03 Hours 30 Minutes are from Friday
6. 01 Hour 30 Minutes are from Monday
7. The TAT is 05 Hours.
8. Note that Saturday(it may be any week day) and Sunday (Weekend) is
eliminated.

I need formula for this calculations.

I use Excel 2000

Date and Time is customized as dd.MM.yyyy hh:mm:ss AM/PM

It will be great help if some body can help me on the above. thanks in
advance.

Thanks and have a great day !!

Karan.

 

[Guest]

See if this helps:

http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours

 

[Guest]

Thanks for your valuable help !

I had been through the link provided, and i feel it should work. I tried to
apply the formula, says contains error. But, i am not able to apply the
given formula in my worksheet. This is the first time that i am working with
such a big formula and i don't know how to change the cell addresses and in
what format i need to enter the time in DayStart and DayEnd. And also very
importantly where do i need to put the Holidaylist, within the formula or in
a seperate column or in a seperate sheet.

i have the following information:
B1 =StartDT = Date and Time => 03.01.2007 03:45:00 PM
J1 = EndDT = Date and Time => 08.01.2007 06:15:00 PM

DayStart = 09:00 AM
DayEnd = 19:00 AM

Holidaylist = 05.01.2007(Holiday) and 07.01.2007(Sunday weekly holiday)

It would be great help, if you can show me how to apply the above details in
the formula below:

To return the total number of working hours, use the following formula:

=IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT(StartDT),HolidayList,0))))
,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))))




Thank you once again for your great help.

have a nice day !!

Karan

 

[Guest]

The variables StartDT,EndDT,DayStart,DayEnd and HolidayList are named ranges.

So for example:

StartDate in B1:

Select B1

Insert=>Name=Define ....Names in worbook: StartDT, Refers to: =B1 (or
Sheet1!B1)

Repeat for the others.

For Holidaylist, select the two cells with holiday dates and then name as
above. It can be on a separate sheet if you wish.

The formula should then work ( I have just done the above exercise) and
result was 22.5 hours.

If you copied the formula from the web site, just check that no "extra"
characters were inserted.

HTH