Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
I am working on a microsft excel spreadsheet for my boss( excel 2000). We are
working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
Hi
I can't think of a way around this without VBA programming. Maybe you could use an extra column and type in a * when the job overruns - to show that the date has been changed. Your formula could then be: =IF(OR(TODAY()L75,M75="*"),"Late","On time") Is that any use to you? Andy. "babiigirl" wrote in message ... I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
This may or may not be possible for you but I would just insert another
column in the sheet to enter the 2nd date. That way you would not be affecting the formulas as you have outlined. This column would only have date data in it if that particular entry was 'Late' "babiigirl" wrote: I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
Hi,
I'm still kinda new at excel. They are wanting me to keep this spreadsheet as simple as possible whne it comes to having to enter numbers. When I add a column though it seems to throw off the whole formula for the "on time" or "late" column. Is there any way to avoid that? babiigirl "Andy" wrote: Hi I can't think of a way around this without VBA programming. Maybe you could use an extra column and type in a * when the job overruns - to show that the date has been changed. Your formula could then be: =IF(OR(TODAY()L75,M75="*"),"Late","On time") Is that any use to you? Andy. "babiigirl" wrote in message ... I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
In adding that new column why does it disable the status column then? Do I
need to change something in my formula? "Tim M" wrote: This may or may not be possible for you but I would just insert another column in the sheet to enter the 2nd date. That way you would not be affecting the formulas as you have outlined. This column would only have date data in it if that particular entry was 'Late' "babiigirl" wrote: I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
Inserting a column should automatically adjust your formulae. What do they,
and the data, look like? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "babiigirl" wrote in message ... Hi, I'm still kinda new at excel. They are wanting me to keep this spreadsheet as simple as possible whne it comes to having to enter numbers. When I add a column though it seems to throw off the whole formula for the "on time" or "late" column. Is there any way to avoid that? babiigirl "Andy" wrote: Hi I can't think of a way around this without VBA programming. Maybe you could use an extra column and type in a * when the job overruns - to show that the date has been changed. Your formula could then be: =IF(OR(TODAY()L75,M75="*"),"Late","On time") Is that any use to you? Andy. "babiigirl" wrote in message ... I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
My data is as follows ( I'm not sure if you need all this information, but I
will go ahead and give it to you) : a date column, then a due date column, invoice amount column,prepress column, then a proof out column, proof completion column and then a status column. The main 2 columns I am working with are the Proof completion column and the status column. In the completion column in the date in which the proof was returned and if it and less then 4 days before the due date it will show up late in the status column. The formula for the completion column is =X78-4. In the status column in order to say whether it is "on time" or "late" the formula is =IF(TODAY()<=L78, "On Time","Late"). I tried inserting a new column, but now the status column will not change status when a new number is entered. I tried the formula I was given in the previous reply to my post as well and it would not work either. Have any idea? babiigirl "Bob Phillips" wrote: Inserting a column should automatically adjust your formulae. What do they, and the data, look like? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "babiigirl" wrote in message ... Hi, I'm still kinda new at excel. They are wanting me to keep this spreadsheet as simple as possible whne it comes to having to enter numbers. When I add a column though it seems to throw off the whole formula for the "on time" or "late" column. Is there any way to avoid that? babiigirl "Andy" wrote: Hi I can't think of a way around this without VBA programming. Maybe you could use an extra column and type in a * when the job overruns - to show that the date has been changed. Your formula could then be: =IF(OR(TODAY()L75,M75="*"),"Late","On time") Is that any use to you? Andy. "babiigirl" wrote in message ... I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
Hi
One way: Rename date column you have now as PlannedDueDate. Beneath it add another column named as ActualDueDate. When PlannedDueDate was p.e. column B, then into C2 enter the formula =IF(B2="","",B2) , and copy it down (you can have this formula ready waiting for an entry into another column). Whenrever you enter planned due date, the same date is displayed as actual due date too. Now when the work will be late, then overwrite the formula with new date - you will have different planned and actual due dates for this work. In formulas referring to due date, you have now to decide, which one you have to refer to. P.e. in status formula, you have to refer to planned due date - the work will be late anyway when planned due date is missed. Arvi Laanemets "babiigirl" wrote in message ... In adding that new column why does it disable the status column then? Do I need to change something in my formula? "Tim M" wrote: This may or may not be possible for you but I would just insert another column in the sheet to enter the 2nd date. That way you would not be affecting the formulas as you have outlined. This column would only have date data in it if that particular entry was 'Late' "babiigirl" wrote: I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complicated formula please help asap!
I believe that is what this forula is doing =IF(TODAY()<=L30, "On
Time","Late") but I am not sure. Should I replace this formula with the one you gave me? My boss set up this sheet and then just handed it to me so I'm not positive on how all the formulas in it work. "Arvi Laanemets" wrote: Hi One way: Rename date column you have now as PlannedDueDate. Beneath it add another column named as ActualDueDate. When PlannedDueDate was p.e. column B, then into C2 enter the formula =IF(B2="","",B2) , and copy it down (you can have this formula ready waiting for an entry into another column). Whenrever you enter planned due date, the same date is displayed as actual due date too. Now when the work will be late, then overwrite the formula with new date - you will have different planned and actual due dates for this work. In formulas referring to due date, you have now to decide, which one you have to refer to. P.e. in status formula, you have to refer to planned due date - the work will be late anyway when planned due date is missed. Arvi Laanemets "babiigirl" wrote in message ... In adding that new column why does it disable the status column then? Do I need to change something in my formula? "Tim M" wrote: This may or may not be possible for you but I would just insert another column in the sheet to enter the 2nd date. That way you would not be affecting the formulas as you have outlined. This column would only have date data in it if that particular entry was 'Late' "babiigirl" wrote: I am working on a microsft excel spreadsheet for my boss( excel 2000). We are working on a spreadsheet dealing with a lot of formulas. I have one column that tells whether a work is lat eor not. This formula automatically enters " late" or "on time" which is great, but if a work is late then i must chnage the date in one of the columns so I know the actual due date. The problem is the column I must change the due date is one of the ones in the formula for the status column and when I change that date for our records it automatically changes the work to on time when in reality it wasn't. I was wondering if there is any way that after I enter that inital date if I can get the status to stay at either "late" or "on time" and it will not chnage when I change the new due date. Is there a way I can adjust the formula to this? This is the formula I am using for the status =IF(TODAY()<=L75, "On Time","Late"). This is the formula for the due date =W75-4. Please help if you can! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complicated formula help required | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Replacing MCONCAT with CONCAT_RANGE (in a complicated formula) | Excel Worksheet Functions | |||
Complicated Pie Chart formula | Charts and Charting in Excel | |||
need help with Index, Match and Countif in the same complicated formula | Excel Discussion (Misc queries) |