calc time
 

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

calc time
 

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.2 5),(CS10-0.25),IF(AND(CN10="Saturday",CT10="Sunday",CP10<=0 .75,CS10<0.25),(0.75-CP10),IF(AND(CN10="Saturday",CT10="Sunday",CP100. 75,CS10<0.25),0,IF(AND(CN10="Saturday",CT10="Sunda y",CP10<=0.75,CS100.25),((0.75-CP10)+(CS10-0.25)),))))))
--

Joe Mac


"kevcar40" wrote:

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

calc time
 

On 7 Sep, 23:46, Joe Mac wrote:
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",CP100. *75,CS10<0.25),0,IF(AND(CN10="Saturday",CT10="Sund ay",CP10<=0.75,CS100.25),*((0.75-CP10)+(CS10-0.25)),))))))
--

Joe Mac



"kevcar40" wrote:
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- Hide quoted text -

- Show quoted text -

thanks for reply however i cant get this to work

calc time
 

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...
--

Joe Mac


"kevcar40" wrote:

On 7 Sep, 23:46, Joe Mac wrote:
Try this...

=IF(AND(CN10="Saturday",CT10="Saturday",CS10<=0.75 ),(CS10-CP10),IF(AND(CN10Â*="Saturday",CT10="Saturday",CS1 00.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",CP100. Â*75,CS10<0.25),0,IF(AND(CN10="Saturday",CT10="Sun day",CP10<=0.75,CS100.25),Â*((0.75-CP10)+(CS10-0.25)),))))))
--

Joe Mac



"kevcar40" wrote:
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- Hide quoted text -

- Show quoted text -

thanks for reply however i cant get this to work