Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
hi everyone
i work for an accident management company and have a few new reports that need some complex conditional formating i will details the requirements below hopefully someone can help. No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. any help you guys can provide would be greatly appreciated. -- Stuart |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
"Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
For the first part select f2 and
Format|conditional formatting|formiula is and paste in =(F2-TODAY())<7 Use the format painter to apply it to the full range of cells. For G7 paste in the conditional format =(G7-F7)28 Is that OK? Mike "Stuart" wrote: hi everyone i work for an accident management company and have a few new reports that need some complex conditional formating i will details the requirements below hopefully someone can help. No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. any help you guys can provide would be greatly appreciated. -- Stuart |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
thanks bob the No Est formulae worked perfect but the G4S Cash Report
formulae didnt work at all could you please advise? -- Stuart "Bob Phillips" wrote: "Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
What do you have in F7 and G7?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... thanks bob the No Est formulae worked perfect but the G4S Cash Report formulae didnt work at all could you please advise? -- Stuart "Bob Phillips" wrote: "Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
G7 is the date the vehicle is due to come onsite, F7 is the date the accident
happened i need to determine the number of days between the 2 dates and if that value is equal to ior greater than 28 then it has a red background with white text. -- Stuart "Bob Phillips" wrote: What do you have in F7 and G7? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... thanks bob the No Est formulae worked perfect but the G4S Cash Report formulae didnt work at all could you please advise? -- Stuart "Bob Phillips" wrote: "Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
I meant what dates do you have? Also, add =ABS(F7-G7) to a cell and see what
you get. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... G7 is the date the vehicle is due to come onsite, F7 is the date the accident happened i need to determine the number of days between the 2 dates and if that value is equal to ior greater than 28 then it has a red background with white text. -- Stuart "Bob Phillips" wrote: What do you have in F7 and G7? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... thanks bob the No Est formulae worked perfect but the G4S Cash Report formulae didnt work at all could you please advise? -- Stuart "Bob Phillips" wrote: "Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating With Formulaes
my bad its colum e is accident date and column f for date booked in the dates
i have in e7 is 09/05/2007 the date in f7 is 14/05/07, yes these dates as are wouldnt meet the 28+ condition but even changing the date in f7 toi a date i know is more than 28 days in difference still does not bring me the desired result. -- Stuart "Bob Phillips" wrote: I meant what dates do you have? Also, add =ABS(F7-G7) to a cell and see what you get. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... G7 is the date the vehicle is due to come onsite, F7 is the date the accident happened i need to determine the number of days between the 2 dates and if that value is equal to ior greater than 28 then it has a red background with white text. -- Stuart "Bob Phillips" wrote: What do you have in F7 and G7? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Stuart" wrote in message ... thanks bob the No Est formulae worked perfect but the G4S Cash Report formulae didnt work at all could you please advise? -- Stuart "Bob Phillips" wrote: "Stuart" wrote in message ... No Estimate Report: i have a date in column F data starts on row 2 if any date in column if is equal to or more than 7 in the past to todays date "=TODAY()" then i want that date in column F to be highlighted in red background with white text. that will then allow me to prioritise which jobs from each bodyshop need to be chased urgently and which ones can wait a day or so this report takes me about 2 days to complete so its important i deal with the urgent ones first hence the need for this formulae. Select all the cells and use a formula of =AND(F2<"",F2<=TODAY()-7) G4S Cash Report: i have the accident date in F7 & the bookin Date in G7 if the difference between these 2 dates is equal to or greater than 28 then i want the date in g7 to have red background and white text this will indicate to me if it the time between the accident and the vehicle coming into the bodyshop is greater than 28 days if it is then i have to move that job to a seperate report so its important i can identify if it has exceeded 28 days or not. =ABS(F7-G7)=28 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Formulaes | Excel Discussion (Misc queries) | |||
Files and formulaes | Excel Worksheet Functions | |||
Help with Macros or formulaes | Excel Discussion (Misc queries) | |||
Help with Macros or formulaes | Excel Discussion (Misc queries) | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) |