| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time Calc 24 hrs | Excel Worksheet Functions | |||
| Time Calc | Excel Worksheet Functions | |||
| calc time value | Excel Discussion (Misc queries) | |||
| Calc F9) Button and time. | Excel Discussion (Misc queries) | |||
| time sheet calc by time in and out | Excel Worksheet Functions | |||
Hybrid Mode
