Conditional Format Tracker by Date

I'm building a tracker to make sure paperwork is done on time.  
Headers:
A:Name
B:IDNum
C:Phone
D:StartDate
E:Plan
F:Paperwork
G:30DAuth
H:90DAuth

I'd like to use conditional formatting to compare the dates and color the 
cells for easier tracking.  

The compare TO date is column D.  
In column E, we have 7 days to write the plan so if there is no date in that 
column, turn red.  When a date is entered turn green if within 7 days but 
yellow if it's past 7 days.  
In column F, same as column E but only 3 days for that one.  (I can figure 
that out if I get a working forumla for column E.)
In column G, we get a date that they're paid through.  This is not connected 
to the start date but is compared to today.  If it's within 20 days of today, 
cell turn yellow.  If within 14 days, turn orange.  If 7 days, turn blue.  
And if its today or later, turn red.  
In column H, same as column G but the days are different.  (If I can get a 
formula for column G, I can just change the numbers.)

Any help with this is greatly appreciated.  I'm running Word03 and I just 
can't figure out the IF statements with the Dates and the TODAY thing.  Can 
you please help???  Thanks in advance.  
--Dax 
-- 
I would give my left hand to be ambidextrous!
0
Utf
2/3/2010 8:14:11 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

0 Replies
1097 Views

Similar Articles

[PageSpeed] 28

Reply:

Similar Artilces:

Conditions in macros
Can anyone let me know why Access insists that conditions in macrosdon't seem to support comparitive operaters for checking ranges, andonly support the Between.. And operators? I had a whole set ofconditions where some were checking between a range of numbers (suchas Age). Where I had 20<=Forms![Form1]![Age]<25, it was ignoring thecondition, but it did work when I changed it to (Forms![Form1]![Age]Between 20 And 25).Cheers,Chris For compound conditions, use this kind of construct: ([Forms]![Form1]![Age] >= 20) AND ([Forms]![Form1]![Age] <= 25)-- Allen Browne - Microsoft MVP...

Incorrect Workers' Comp posting date
When posting Worker's Compensation Tax for payroll, the wrong posting date was used (03/28/09 instead of 02/28/09). This hasn't been posted to the GL, so the payroll module is the only thing we're worried about. Is there a way to void this posting to payroll and start over or change the dates to correct this? Thanks! If I am not mistaken, it does not actually post to the Payroll Side, just delete the gl batch and run it again with the correct date. "danrose03" wrote: > When posting Worker's Compensation Tax for payroll, the wrong posting date > was us...

how can i apply conditional formatting by code ?
Say that there is a table T_PERSONAL [Name (text), Surname (text), Salary (integer)]. I created a Tabular Form (F_PERSONAL) which retrieves information from T_PERSONAL. I want to put a image for a specific record on the form, if the record satisfy some specific criteria. For other record which does not satisfy the criteria, image should be invisible. Say that, if [Name (text)] ~text13~ is "ALBERT", Image Object ~Image13~ should be visible. I dont suppose that Image Object should be included in T_PERSONAL. It will be inserted manualy during design stage of Tabular Form. I would not as...

file format needs to be changed
I have an excel file which comprises of a template for Identity card with a jpg file embedded in it. I would like to save this file in a jpeg format so as to get a photo printout of the same. How is this possible? If you scan the document, that would put the file into an image format. Then you could convert the file to whatever image file format you wanted. Just a thought... Highlight the area you want printed then hold SHIFT key and select Edit>Copy Picture. Open your favorite graphics editor or MS Paint and paste to there. Save as *.jpg Gord Dibben Excel MVP On Tue, 14 Jun 200...

Converting a string date field/node in an XML document to Date type in a dataset
I am having problems with Date Strings when using VS.net method of converting an XML document to a dataset. I need the the date nodes to be of date type. As I am loading the Dataset to a datagrid, and I using the sort facility to sort by date. Currently the dates are being treated as strings. ree32@hotmail.com (ree32) wrote in message news:<7606ccc8.0409192108.3cbebe24@posting.google.com>... > I am having problems with Date Strings when using VS.net method of > converting an XML document to a dataset. > > I need the the date nodes to be of date type. As I am loading the ...

date format #18
Hi all, I have a spreadsheet containing lots of pivot tables. The source data contains date formulas in the following ways: =IF(FR5="","",TEXT(FR5,"yyyy-mm")) this returns dates like----- 2007-11 =IF(GM5="","",TEXT(GM5,"mmm-yy")) this returns dates like------- Nov-07 As a result, when I try to sort my pivot table data by date, the dates that are shown as Nov-07 are recognised as text therefore only allowing me to sort by alphabetical order. The dates that are like 2007-11, i am able to sort it by the earliest/latest dates. I...

Conditional Printing of Certain Fields in A Report
i need to have a conditional printing of some fields (SEE BELOW) VENDOR SHARE $999.99 LESS TAX $999.99 country ENGLAND tax (%) 20% AMT DUE $999.99 how do I print only these 5 lines when the tax amount is not zero. Also how do i combine line 2,3 & 4 and looks like LESS ENGLAND (20%) TAX instead. I tried doing the following: ="LESS" & [COUNTRY] &"(" &[TAX (%)] &")" in the control source but i got a #ERROR error Any help would be appreciated. Kevin T <Kevin T@discussions.microsoft.com> wrote: >i need to...

Question on Sorting by (date, etc)
I have a column (A) which sre dates. If I select the column heading and click either the sort ascending or desending button, the date column alone sorts independent of the other eight columns that comprise the entire entry. How would I go about being able to sort by a particular column and have everything sort with it? I have other columns that I would like to sort by sometimes too, like "color", "type", etc. Thanks for reading! select all the columns and now go to data | sort . On Feb 20, 11:21=A0pm, "Ed" <2...@333.com> wrote: > I have a column...

formatting #4
Hi, is it possible to automatically display those cells with a formula with a different format. Eg I would like 'input' cells in green, and 'calculated' cells in red. Thanks. Change color numbers to suit Sub colorcells() Dim c As Range For Each c In Selection If c.HasFormula Then c.Interior.ColorIndex = 4 Else c.Interior.ColorIndex = 6 End If Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "KRK" <trebor@yeleek.nospam.freeserve.co.uk> wrote in message news:ONIaRcClJHA.5732@TK2MSFTNGP05.phx.gbl... > Hi, i...

Copy worksheets and formats
Hi, I have a worksheet (budget) set up as 31 columns representing days of a month. I also have a worksheet designated as yearly, 12 columns for each month. The first worksheet, Jan, totals numerous rows and sends the data to the Jan column of the monthly WS. I need to copy the Jan worksheet in a way it will change the Jan! to Feb!, then Mar!, etc. If I copy it as is the data will be the same as the month of Jan. Hope you can understand this. I can't ;+) Ken More than one way, but an easy way if each month formulas are the same is to copy Jan sheet, and paste them into the Feb a...

Charting data by date
Hi, I'm new to Excell 2002, and have made worksheets with data refreshed from the Web. However I can't get it to show up on a chart with dates on the X axis. Have the axis formatted OK, but how does it collect its data? Have tried a simple worksheet with manually entered data & the dates entered in column A, but still can't get a chart to work. ...June. Jon Peltier has a charting tutorial that may help you get started: http://www.peltiertech.com/Excel/ChartsHowTo/index.html When you manually enter data and dates, what problems do you have with the chart? June wrote: >...

using date function, month shows as January when i type (12)
when date of birth is A1 and i use the formula =date(year(A1)+19,month(12),day(31)), to get the last day of the year before the 20th birthday, 31/1/1919 appears instead of 31/12/1919. why is this? if i take the +19 away, the same thing happens. The only time it works is if i specify the year instead of using another cell. Does this formula do what you need? ="12/31/"&YEAR(A1)+19 The reason your original formula did not work is because the MONTH() and DAY() functions require serial date numbers (that is to say, an Excel date equivalent expressed as a number). =DATE(YEA...

how to replace the date
hi kabel, i have put the date 03/03/2004 in a worksheet in various cells, i woul like to replace that date with 03/04/2004. pls help me -- Message posted from http://www.ExcelForum.com Hi try the following - goto 'Edit -Replace' - enter your existing date and your new date P.S.: please stay in the original thread P.P.S. My first name is Frank :-) -- Regards Frank Kabel Frankfurt, Germany > hi kabel, > i have put the date 03/03/2004 in a worksheet in various cells, i > would like to replace that date with 03/04/2004. > pls help me. > > > --- > Message p...

How do I sum dollars in one column based on dates in another?
How can I sum the total numbers contained in one column based on dates in another column? Example: I need the total of 5 units sold int the month of January 2006. I have the sale ammounts in one column and the closing dates in another. What forumla would I use for this? Thanks in advance for any help. =SUMPRODUCT(--(MONTH(B2:B50)=1),--(YEAR(B2:B50)=2006),A2:A50) for January change 1 in the month part to a different month -- Regards, Peo Sjoblom Northwest Excel Solutions www.nwexcelsolutions.com (remove ^^ from email address) Portland, Oregon "Alan" <Alan@discus...

Validate Purchase Order Date
We were able to enter a PO (against a Project) dated 2004, several years before we started on GP, so I'd guess that (because this is non-posting) the date isn't validated against the fiscal periods that have been set up. However, this lead to a Project Begin Date of 2004, and caused an error when we tried to enter a fixed fee amount against the project "Fiscal information does not exist for the date range. The fiscal year must exist in order to create the periodic records". The only way I found to fix this was to go into SQL table PA01201 and fix the Project's b...

user form
I am so frustrated. I am creating a form and I want to force a phone number format. Easy in Excel but cannot find it anywhere in word 2007. Please help Thanks Use the VBA Format function Format(1234567890, "(###) ###-####") will return (123) 456-7890 -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Dcook" <Dcook@discussions.microsoft.com> wrote in message news:5F0E1C93-E828-46F7-98EF-FE6DF84...

E-mail Templates HTML format with pictures
Is it possible to create custom templates with an HTML body, including like my company logo ? If this is possible, can any one help me.. Thank you again in advance I created a web page in MS FrontPage. Posted the page on a web server with a public address. Then copied the page into a Direct Email Template. If you don't require a logo you can just paste it from FrontPage right into the Direct Email Template. HTH Donnie Sweat "Ulrich" <Ulrich@discussions.microsoft.com> wrote in message news:A2BF4B7E-D92E-41EC-A55A-7B53AEE60A22@microsoft.com... > Is it possible ...

Calculate difference between 2 date and times with average
I have used the formula: Start time End time 6/9/2007 10:35 AM 6/9/2007 3:30 PM 4:55 Formula Description (Result) =Text(b1-a1,"d:h:) But when I use the text function, I cannot calculate an average. Also, if you know a way to calculate the difference and exclude weekends... As for excluding weekends, investigate the NETWORKDAYS formula in Help. But if your workday is not 24 hours long, and you have holidays to contend with in addition to weekends, the problem is fairly complex. Here are the steps: 1. If StartDate and EndDate are on the same date, hours worked = number of hou...

Query Date Help W/Avg work days
please note that in order to get the date 6/28/1979 I am using average work days each month of 21.75 I have tried to Add just the total days to the date but it's not correct because I need it to be based off the 21.75 days per month not actually days. Total Years of Prior Service Days Hire Date Adjusted Hire Date 7 years 4 months 6 days 2685 6/15/1987 2/7/1980 (s/b 2/9/80) 1 year 8 months 13 days 620 2/10/2005 6/1/2003 (s/b 5/27/03) This is what is being calculated so far once I run the query. "aldunford" wrote: > This worked great thanks so much!! I have ...

Workflow - date and time
hi I have a field called enrolment date which is usually lesser than the created on date. i want a workflow which says, when a new contact is created wait for 5 days after enrolment date and then create - 1st mailer task wait for 2 months and create - 2nd mailer task. i have got a workflow and it says when a new patient is created wait 5 days after contact.enrolmentdate if case 1 create mailer 1 end if if case 2 create mailer 2 end if however, both mailer tasks get create right away rather than waiting for 5 days and 2 months. in the wait for timer i ticked revaluate expressision whe...

Conditional formatting #50
Hi, The worksheet I've been working on uses paste links to carry forward workers details from one worksheet to the next. These cells are left unprotected so the chain can be broken if a person leaves, and someone new has to be inserted. This is fine but it makes it tricky to spot where breaks occur. Can conditional formatting tell the difference between a formula and text entered? If so can someone please give me an example that I can use. Many thanks. DonH You would need to build a simple UDF Function IsFormula(rng as range) IsFormula = rng.HasFormula End Function and use t...

Conditional copy cells from one excel file into current
Hello All, Does a macro (or function or ??) exist to conditionally copy value from a stored spreadsheet into the current (open) spreadsheet? Here's the scenario: Stored spreadsheet is two columnsof text (~200 rows) : Think explici question in column one, then the correct answer in column two of th same row. Then my current (open) spreadsheet has SOME of the same questions i column one. I would like to invoke a [macro?] that, in pseudo code, does somethin similar to the following: for each row (question in column 1) of the current spreadsheet Search in column 1 of *stored* spreadshee...

Adding Dates
I have a field with arrival date then a field with days and another field with departure date. I enter a date in the arrival date field, then I put in how many days as a number in the days field and using a Macro command on exit I want the departure date to show automatically the arrival date plus the days. How do I do it ? On Mon, 28 Jan 2008 19:58:48 +0200, Ange Kappas wrote: > I have a field with arrival date then a field with days and another field > with departure date. I enter a date in the arrival date field, then I put in > how many days as a number in the days fiel...

Date and hour arrangement
Hi all Apperiacite for your help, I have list of date and hour with below format dd.mm.yyyy hh:mm:ss am/pm dd.mm.yyyy for dd.mm.yyyy is belong to hr 00:00:00, so my question here, i'm using this queries to get hourly; Hour: IIf(Trim(Mid([PERIOD_START_TIME],12,2)) Is Null,"00",Trim(Mid( [PERIOD_START_TIME],12,2))) this queries is to get, when "dd.mm.yyyy" put 00, else put hh, but seems like wrong queries, due to result is still NULL for non hh, but with hh is working good. please correct me. thank very -mohsin -- Cheers Mohsin Message posted via http://www.ac...

How to copy custom number formats
Hello All I have a custom number format for a Excel Cell A1 which is #,##0.00 "CAD" and in A1 I now have a value 1000 CAD. I have assigned cell B1 as =A1 but an external application when the report is run populated now cell A1 as 20000 MXP and now my cell B1 says 20000 CAD and not 20000 MXP and when I check the format for B1 it still as the format as #,##0.00 "CAD" whereas now the format of my cell A1 is #,##0.00 "MXP" appears only the value and not the format is being copied. Can any of you suggest a workaround to resolve this problem. Many thanks Simon ...