calc time

[kevcar40]

hi
i have a spreadsheet containing days and times a fault occurred on a
machine
the faults can occurr any time over a 24 hr period
what i want to do is
on a saturday stop the count after 18:00:00
and on a sunday start the count at 06:00:00
if the fault runs through the saturday and sunday i want to ignore
18:00:00 to 06:00:00
the column holding the start day is CN
the column holding the start time is CP
the column holding the End time is CS
the column holding the End day is CT

Start day Start time End time End
day Total down time
Saturday 08:30:00 17:30:00
Saturday 9:00:00
Saturday 08:30:00 19:30:00
Saturday 9:30:00
Saturday 07:30:00 04:30:00
Sunday 0:30:00
Saturday 17:30:00 08:30:00
Sunday 3:00:00
Sunday 05:30:00 08:30:00
Sunday 2:00:00

thanks for any help

kevin

 

[Joe Mac]

Try this...

=IF(AND(CN10="Saturday",CT10="Saturday",CS10<=0.75),(CS10-CP10),IF(AND(CN10="Saturday",CT10="Saturday",CS10>0.75),(0.75-CP10),IF(AND(CN10="Sunday",CT10="Sunday",CP10<=0.25),(CS10-0.25),IF(AND(CN10="Saturday",CT10="Sunday",CP10<=0.75,CS10<0.25),(0.75-CP10),IF(AND(CN10="Saturday",CT10="Sunday",CP10>0.75,CS10<0.25),0,IF(AND(CN10="Saturday",CT10="Sunday",CP10<=0.75,CS10>0.25),((0.75-CP10)+(CS10-0.25)),))))))

 

[kevcar40]

Try this...

=IF(AND(CN10="Saturday",CT10="Saturday",CS10<=0.75),(CS10-CP10),IF(AND(CN10­="Saturday",CT10="Saturday",CS10>0.75),(0.75-CP10),IF(AND(CN10="Sunday",CT1­0="Sunday",CP10<=0.25),(CS10-0.25),IF(AND(CN10="Saturday",CT10="Sunday",CP1­0<=0.75,CS10<0.25),(0.75-CP10),IF(AND(CN10="Saturday",CT10="Sunday",CP10>0.­75,CS10<0.25),0,IF(AND(CN10="Saturday",CT10="Sunday",CP10<=0.75,CS10>0.25),­((0.75-CP10)+(CS10-0.25)),))))))
--

Joe Mac








- Show quoted text -

thanks for reply however i cant get this to work

 

[Joe Mac]

Kevin...
What is going wrong in the formula? I noticed that when I copied the
formula from the post (I didn't save it locally) and pasted into a
spreadsheet this morning that this process inserted a number of "-" hyphens
into the pasted formula (5 to be exact)... I stripped those miscellaneous
hyphens from the formula & all worked fine...