Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help with formula
Hi
I have a worksheet with column C (start time) & column D (end time). (C1:D50) e.g C D 07:45 08:50 11:50 12:50 In Cells A1 and B1 i have the start break time and end break time '08:00' and '08:20' for the first break. In Cells A2 and B2 i have the start break time and end break time '12:00' and '12:30' for the first break. What i want to do is calculate the amount of time lost but exclude both break times can anyone help? |
#2
|
|||
|
|||
Chip Pearson has a timehseet example with formulas on his
website: http://www.cpearson.com/excel/overtime.htm HTH Jason Atlanta, GA -----Original Message----- Hi I have a worksheet with column C (start time) & column D (end time). (C1:D50) e.g C D 07:45 08:50 11:50 12:50 In Cells A1 and B1 i have the start break time and end break time '08:00' and '08:20' for the first break. In Cells A2 and B2 i have the start break time and end break time '12:00' and '12:30' for the first break. What i want to do is calculate the amount of time lost but exclude both break times can anyone help? . |
#3
|
|||
|
|||
paste this formula into cell E1
=60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0)) "Kevin" wrote: Hi I have a worksheet with column C (start time) & column D (end time). (C1:D50) e.g C D 07:45 08:50 11:50 12:50 In Cells A1 and B1 i have the start break time and end break time '08:00' and '08:20' for the first break. In Cells A2 and B2 i have the start break time and end break time '12:00' and '12:30' for the first break. What i want to do is calculate the amount of time lost but exclude both break times can anyone help? |
#4
|
|||
|
|||
Oops, I just tested that and it fails in the situation where both the start
time is before 8:00AM and the end date is after 8:20AM. Thus, we need to add one more IF() to the formula. Try this one istead =60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0)-IF(AND(C1<$A$1,D1$B$1),$B$1-$A$1,0)) "Duke Carey" wrote: paste this formula into cell E1 =60*24*((D1-C1)-IF(AND(D1$A$1,D1<$B$1),D1-$A$1,0)-IF(AND(C1$A$1,C1<$B$1),$B$1-C1,0)) "Kevin" wrote: Hi I have a worksheet with column C (start time) & column D (end time). (C1:D50) e.g C D 07:45 08:50 11:50 12:50 In Cells A1 and B1 i have the start break time and end break time '08:00' and '08:20' for the first break. In Cells A2 and B2 i have the start break time and end break time '12:00' and '12:30' for the first break. What i want to do is calculate the amount of time lost but exclude both break times can anyone help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF & VLOOKUP FORMULA | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Help with macro formula and variable | Excel Worksheet Functions | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |