how to subtract weekends and off business hour from an existing time

[lilian]

hi all

i have a query which i hope u folks can shed some light

question
how do i subtract weekend and off business hour time from
consolidated time , is there any formula that i can remove the weeken
and off business hour directly from the consolidated time, reason why
m asking for help is that these are old data and the administrator ha
left the company without providing a trace of the raw data and the onl
thing left is the sheet of numeric like below.

e.g

the time is 587:14:35( average time for the entire week of wor
included weekend and non business hour)

what to exclude
sat and sun = 24hrs each X 2= 48:00:00
weekday non business hour = 13hrs x 5 =65:00:00

ur help and advice is appreciated

thks

;

 

[Frank Kabel]

Hi
IMHO you won't be able to calculáte the net working time without access
to the raw data. You'll need at least the starting time for a week and
the ending time for a week.

sorry to say

 

[Dave Peterson]

You could put 113:00:00 in a cell.
copy it
select your range to adjust and edit|paste special|but click Subtract.

Or you could use a formula:

=a1-time(48,0,0)-time(65,0,0)

And drag down.

I like the formula approach. If I mess it up, I still have the original values.

 

[lilian]

Hi Dave

thks for the help and advice, have the following queries why 2 answer
return are different, i have also convert the time format to hh:mm:ss
any advice on this will be appreciated

And also thks to frank for looking into this ...




using the 113:00:00 cell
06:37:31 03:06:27 14:26:44 18:54:10

using the formula = =a1-time(48,0,0)-time(65,0,0)
06:37:31 03:06:27 09:33:16 05:05:50


the original time is
P95 Full ETTR -191:37:31
Sub ETTR - 164:06:27
Time to solve - 26:33:16
Reactivity Time - 46:05:5

 

[Frank Kabel]

Hi
try formating your cells with the custom format [hh]:mm:ss so that the
hours won't rollover after 24 hours.