Date Functions & Formatting

I'm putting together a spreadsheet showing personnel recurring training dates 
and whether they are overdue or current in that training. I have about 100 
people with about 50 different training requirements that are recurring on 
different timelines (ex: Mark Smith has to take CPR class every 20 months and 
a driving course every 12 months). I'm trying to develop a function/format 
combo that lets me type in the date they last had that training, paired with 
the required frequency of the training, giving me a result of "CURRENT" or 
"OVERDUE" and turning the cell green or red. I've played around with the 
TODAY() function a little bit, but I can't seem to figure anything out. 
0
Utf
1/24/2010 1:21:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

9 Replies
783 Views

Similar Articles

[PageSpeed] 51

On Sat, 23 Jan 2010 17:21:01 -0800, goskerz <goskerz@discussions.microsoft.com>
wrote:

>I'm putting together a spreadsheet showing personnel recurring training dates 
>and whether they are overdue or current in that training. I have about 100 
>people with about 50 different training requirements that are recurring on 
>different timelines (ex: Mark Smith has to take CPR class every 20 months and 
>a driving course every 12 months). I'm trying to develop a function/format 
>combo that lets me type in the date they last had that training, paired with 
>the required frequency of the training, giving me a result of "CURRENT" or 
>"OVERDUE" and turning the cell green or red. I've played around with the 
>TODAY() function a little bit, but I can't seem to figure anything out. 

How best to do this may depend, in part, on your version of Excel.

Setup a table as follows:

A2:An	Names
B1:xx1	Classes

Set up a lookup table someplace named Training Requirements.

Assuming your requirements can all be expressed in months (or years), list the
Class name in one column, with the number of months in the second column. These
names should match exactly the names in B1:xx1 (row 1)

e.g.

CPR	20
Driving 12

A2:	Mark Smith
B1:	CPR
C1:	Driving

Then in B2:xx2 you will enter the date last taken

For a conditional formatting formula

	Select B2
	Use for Format 1 (green):
 =AND(LEN(B2)>0,EDATE(B2,VLOOKUP(B$1,TrainingRequirements,2,FALSE))>=TODAY())

	Use for Format 2 (red):
 =AND(LEN(B2)>0,EDATE(B2,VLOOKUP(B$1,TrainingRequirements,2,FALSE))<TODAY())

Then copy the formats to the applicable cells.

If you are using a version of Excel prior to 2007, you may get an error message
if you cannot use Analysis ToolPak functions.  (I can't recall if they are
allowable in the older versions of Excel.

If that is the case, you will need to set up a matrix of cells someplace, using
the above formula, to generate the actual due dates.  In that case, assume you
set this up in AB2:xxnn, then your conditional formatting formula would be, for
B2, something like:	=and(len(b2)>0,ab2>=today())  for the green and
			=and(len(b2)>0,ab2<today())  for the red

--ron
0
Ron
1/24/2010 3:54:53 AM
Sir, I have a very good feeling about this. I think it's going to work, just 
having a little bit of trouble... For the conditional formatting formulas, 
where do I type those in? I went to manage rules, new rule then "use a 
formula to determine which cells to format" I copied your first formula in 
there but recieved an error message. I am running office 2007. I wish I was a 
little smarter on Excel, but I'm most likely doing something wrong. Thank you 
for your help!
"Ron Rosenfeld" wrote:

> On Sat, 23 Jan 2010 17:21:01 -0800, goskerz <goskerz@discussions.microsoft.com>
> wrote:
> 
> >I'm putting together a spreadsheet showing personnel recurring training dates 
> >and whether they are overdue or current in that training. I have about 100 
> >people with about 50 different training requirements that are recurring on 
> >different timelines (ex: Mark Smith has to take CPR class every 20 months and 
> >a driving course every 12 months). I'm trying to develop a function/format 
> >combo that lets me type in the date they last had that training, paired with 
> >the required frequency of the training, giving me a result of "CURRENT" or 
> >"OVERDUE" and turning the cell green or red. I've played around with the 
> >TODAY() function a little bit, but I can't seem to figure anything out. 
> 
> How best to do this may depend, in part, on your version of Excel.
> 
> Setup a table as follows:
> 
> A2:An	Names
> B1:xx1	Classes
> 
> Set up a lookup table someplace named Training Requirements.
> 
> Assuming your requirements can all be expressed in months (or years), list the
> Class name in one column, with the number of months in the second column. These
> names should match exactly the names in B1:xx1 (row 1)
> 
> e.g.
> 
> CPR	20
> Driving 12
> 
> A2:	Mark Smith
> B1:	CPR
> C1:	Driving
> 
> Then in B2:xx2 you will enter the date last taken
> 
> For a conditional formatting formula
> 
> 	Select B2
> 	Use for Format 1 (green):
>  =AND(LEN(B2)>0,EDATE(B2,VLOOKUP(B$1,TrainingRequirements,2,FALSE))>=TODAY())
> 
> 	Use for Format 2 (red):
>  =AND(LEN(B2)>0,EDATE(B2,VLOOKUP(B$1,TrainingRequirements,2,FALSE))<TODAY())
> 
> Then copy the formats to the applicable cells.
> 
> If you are using a version of Excel prior to 2007, you may get an error message
> if you cannot use Analysis ToolPak functions.  (I can't recall if they are
> allowable in the older versions of Excel.
> 
> If that is the case, you will need to set up a matrix of cells someplace, using
> the above formula, to generate the actual due dates.  In that case, assume you
> set this up in AB2:xxnn, then your conditional formatting formula would be, for
> B2, something like:	=and(len(b2)>0,ab2>=today())  for the green and
> 			=and(len(b2)>0,ab2<today())  for the red
> 
> --ron
> .
> 
0
Utf
1/24/2010 6:14:01 PM
On Sun, 24 Jan 2010 10:14:01 -0800, goskerz <goskerz@discussions.microsoft.com>
wrote:

>Sir, I have a very good feeling about this. I think it's going to work, just 
>having a little bit of trouble... For the conditional formatting formulas, 
>where do I type those in? I went to manage rules, new rule then "use a 
>formula to determine which cells to format" I copied your first formula in 
>there but recieved an error message. I am running office 2007. I wish I was a 
>little smarter on Excel, but I'm most likely doing something wrong. Thank you 
>for your help!

Having 2007 makes things a bit easier.

What was the error message?

Did you properly NAME the lookup table?

What, exactly, is the formula? (Copy/Paste it here please).
--ron
0
Ron
1/25/2010 1:58:47 AM
I had the table embedded in a different sheet in the document. Moved it over 
to the same sheet as the rest of my work and it worked. Thank you, Sir.
"Ron Rosenfeld" wrote:

> On Sun, 24 Jan 2010 10:14:01 -0800, goskerz <goskerz@discussions.microsoft.com>
> wrote:
> 
> >Sir, I have a very good feeling about this. I think it's going to work, just 
> >having a little bit of trouble... For the conditional formatting formulas, 
> >where do I type those in? I went to manage rules, new rule then "use a 
> >formula to determine which cells to format" I copied your first formula in 
> >there but recieved an error message. I am running office 2007. I wish I was a 
> >little smarter on Excel, but I'm most likely doing something wrong. Thank you 
> >for your help!
> 
> Having 2007 makes things a bit easier.
> 
> What was the error message?
> 
> Did you properly NAME the lookup table?
> 
> What, exactly, is the formula? (Copy/Paste it here please).
> --ron
> .
> 
0
Utf
1/27/2010 9:45:01 PM
On Wed, 27 Jan 2010 13:45:01 -0800, goskerz <goskerz@discussions.microsoft.com>
wrote:

>I had the table embedded in a different sheet in the document. Moved it over 
>to the same sheet as the rest of my work and it worked. Thank you, Sir.

Yup.  That is one of the limitations of the formulas used for conditional
formatting -- they must refer to the same sheet (there are other limitations,
too).

Glad you've got it working.  Thanks for the feedback.
--ron
0
Ron
1/28/2010 2:51:01 AM
Ron

You can refer to another sheet for CF if you define a name for cell on other
sheet.

i.e.    defined name "apple" refers to Sheet2A1

On Sheet1  in B2  CF>Formula is:  =B2<>apple

Also can use use other workbooks if in VBE you add reference to other
workbook.

I have many UDF's in an add-in.

I set my workbook to reference the add-in and I can use UDF's from that
add-in in my CF formulas.


Gord


On Wed, 27 Jan 2010 21:51:01 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Wed, 27 Jan 2010 13:45:01 -0800, goskerz <goskerz@discussions.microsoft.com>
>wrote:
>
>>I had the table embedded in a different sheet in the document. Moved it over 
>>to the same sheet as the rest of my work and it worked. Thank you, Sir.
>
>Yup.  That is one of the limitations of the formulas used for conditional
>formatting -- they must refer to the same sheet (there are other limitations,
>too).
>
>Glad you've got it working.  Thanks for the feedback.
>--ron

0
Gord
1/28/2010 7:10:17 PM
On Thu, 28 Jan 2010 11:10:17 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>Ron
>
>You can refer to another sheet for CF if you define a name for cell on other
>sheet.
>
>i.e.    defined name "apple" refers to Sheet2A1
>
>On Sheet1  in B2  CF>Formula is:  =B2<>apple

I wonder why the OP had different results when he moved the table to the same
sheet.  Has this always worked this way?  


>
>Also can use use other workbooks if in VBE you add reference to other
>workbook.
>
>I have many UDF's in an add-in.
>
>I set my workbook to reference the add-in and I can use UDF's from that
>add-in in my CF formulas.

Yes, I do that too and I have all of my custom functions and macros in an
add-in.
--ron
0
Ron
1/28/2010 8:08:48 PM
As far as I know using a defined name from another sheet has always been
allowed in CF

I have not been following the thread so don't know what OP had originally
set up or what problem got cleared up by moving a table.


Gord

On Thu, 28 Jan 2010 15:08:48 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>I wonder why the OP had different results when he moved the table to the same
>sheet.  Has this always worked this way?  

0
Gord
1/28/2010 8:34:33 PM
On Thu, 28 Jan 2010 12:34:33 -0800, Gord Dibben <gorddibbATshawDOTca> wrote:

>As far as I know using a defined name from another sheet has always been
>allowed in CF
>
>I have not been following the thread so don't know what OP had originally
>set up or what problem got cleared up by moving a table.
>
>
>Gord
>
>On Thu, 28 Jan 2010 15:08:48 -0500, Ron Rosenfeld <ronrosenfeld@nospam.org>
>wrote:
>
>>I wonder why the OP had different results when he moved the table to the same
>>sheet.  Has this always worked this way?  

What he wrote was that he received an error message (otherwise unspecified)
initially.  When I asked some questions to try to figure out the problem, he
wrote that the problem was solved by moving a lookup table used in the
conditional formatting formula from a different sheet to the same sheet.
--ron
0
Ron
1/29/2010 3:15:14 AM
Reply:

Similar Artilces:

Validate the format of a number
I need to determine that an entered serial number is valid. It must check that it is 11 characters and follows the format as follows: a letter, followed by a number, followed by 2 letters, followed by 6 numbers, and ending with a letter. For example, the user enters D7PM234567B and the cell next to it would indicate 'valid' or something similar. If 87PM2345674 was entered, it was indicate 'invalid' next to it or something similar to alert the user it is not in the correct format. Thank you very much in advance. Steve This formula =AND(LEN(A1)=11,CODE(...

Convert Date YYYYMMDD
Hi All, I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD. How do I do this? Thanks Matt -- Matt Campbell mattc (at) saunatec [dot] com Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200803/1 Format([YourDateField], "yyyymmdd") -- KARL DEWEY Build a little - Test a little "mattc66 via AccessMonster.com" wrote: > Hi All, > > I need to convert using a query the date format from MM/DD/YYYY to YYYYMMDD. > > How do I do this? > > Thanks > Matt > > -- >...

View pointer in static function Help!!!
Help please , i need to implement a static function in CView derived class that must return to CDoc derived class the active view pointer...how can i do? i tried something similar CSdicap10View* CSdicap10View::GetView() { CSdicap10View* pView = GetActiveView(); return pView; } it return error GetActiveView undeclared identifier or also CSdicap10View* CSdicap10View::GetView() { return this; } but it return static member functions don't have this pointer and also CSdicap10View* CSdicap10View::GetView() { CFrameWnd * pFrame = (CFrameWnd *)(AfxGetApp()->m_pMa...

Date based coding
Need a little help. I have a form that the users use to enter in vacation days onto a calendar. What I would like to do is allow them to make changes to this calendar but prevent them from changing things after the fact. Example if they were scheduled off on 11-16 they must change details for that date on or prior to the 16th. Is this possible? Hi Lori, to validate a record and prevent it from being saved, put code in the form BeforeUpdate event '----------------- make sure all required data is filled 'make sure SomeControlName is filled out If IsNull...

Want to format data
I have data in Excel that is in 2 columns as below Coloum 1 Coloum2 000199 CAON00000013 000200 CAON00000011 I wanted to create my SQL script from these columns as below Update apdoc set user1 = 'Coloum2 value' where refnbr = 'Colum1 value' As I have thousands of lines I have tried concatenate etc but it does not come out ok as the ' don't come out ok and then leading 000 disappear in the concatenate. I would really appreciate some help on this. Thanks Sam Is this an Excel question or an SQL question? Your post says you hav...

Report using dates as a criteria
Hi I have a register (list) that has headings such as name, start date, end date, course duration in days (4) example is Name Start Date End date Duration Sid 25 Feb 2010 3 mar 2010 5 Sid 12 Mar 2010 16 mar 2010 3 As showed each name will have multipule lines as will attend many course. What I need to do is consolidate the list to show how many days they undertook training each month. The report has heading as below Name Jan Feb Mar Apr etc Sid 0 2 6 any and alll help gratef...

XML formatting to HTML...beginner question
Hi, I apologize if I am posting this in the wrong forum. I am creating a .net 2.0 website using vb.net. I have an XML file with the following general format: <LearningObjectives> <lo number = '1'> <Title>First Learning Objective. Click for more details</Title> <details>These are the details for LO 1</details> </lo> <lo number = '2'> <Title>Second Learning Objective. Click for more details</Title> <details>These are the details for LO 2</details> </lo> <lo number = '3'> <Title>Thir...

Item Report by Date
hello all. looking for report that I can filter by date range that will show: department, category, item lookup, description, quantity sold (even if 0), quantity on hand, total sales amount $, profit margin %, profit margin $, last sold date. The movement report is close but I can only get it to show items that have sold. I need to include all items even if they havn't sold. Any ideas? THANKS! ...

Date chart formula question?!?1
Hi all, In a very old spread sheet i was using this formula =IF(OR(AND($D7<G$6,$E7>F$6),AND(F$6>=$D7,F$6<=$E7)),".","") and it worked for dates pre 2009. However, i now wish to use it for dates between 2010 - 2011. Each column represent a week and the rows will indicate and project or task. can anyone help? There is nothing it that formula that will make it work for pre-2009 and not for later years. Perhaps you need to look at your data values? If you have a problem, you could tell us what values are being fed into the formula when it works...

Formatting a range of cells doesn't work
Hi All, I have a workbook exported by Access. Selecting a unique cell or a range of cells the cell formatting doesn't work neither chosen from the menu nor right-clicking on the cell (the formatting dialog-box doesn't come up at all). Selecting an entire column or row cell formatting works normally. What is the cause of this and how to fix the problem? Regards, Stefi Not heard of this before, but if it's a single sheet you could Insert, Worksheet, copy the old worksheet, select cell A1 on the new worksheet and Paste Special, Values (assuming there was no intended formatting ...

Ccur function
I am using the Ccur function to convert a number to a currency type. It works fine except in the cases where the number is negative. Is there a way to get a negative currency value? Correction: The case it fails on is this Ccur( 0 & [number] ). If the field is blank...make it $0.00, else make it $number. Fails when [number] is negative >-----Original Message----- >I am using the Ccur function to convert a number to a >currency type. It works fine except in the cases where the >number is negative. Is there a way to get a negative >currency value? >. > Thi...

Issue with PROPER function
Hi there, I have the following problem: I am attempting to clean up some addresses using the PROPER function. This works really well until I come to PO Box numbers. My problem is that this makes PO Po. I also have entries such as P O Any suggestions on how I can do the following: Make the rest of the address proper and not the PO part and also cater for keeping P O as is. Thanks Provide some Addresses as Sample. -------------------- (Ms-Exl-Learner) -------------------- "Mustang" wrote: > Hi there, > > I have the following problem: ...

Displaying add-in function on ribbon bar in Excel 2007
Hi, I have a small Excel add-in that contains a library of standard functions that I use on a regular basis. Is there an easy way for me to expose these functions in some sort of list format on the new Fluent ribbon bar in Excel 2007? Thanks, Schiz Hi, The operant word here is "easy". I think someone is going to have to write code for you to do this, I don't believe there is an easy way to do it, ie. using one of the built-in features in Excel. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Schizoid Man" wrote: > Hi, > ...

COUNTIFS with a date for criteria...
I want to count all the entries in column N that occurred between a range of dates specified in the other criteria. The dates are obviously in column B. I use this currently- COUNTIFS(Data!N:N,"=Option",Data!B:B,">=1/1/2009",Data!B:B,"<=2/28/2009") However, I use this similar function in multiple cells, all counting different attributes. Rather than going into each cell and changing the search dates, I wanted a criteria to be a variable that can be entered into a different set of cells... like this. COUNTIFS(Data!N:N,"=Option",D...

Problem with formula depending on date.
Soory, if this problem is already resolved. I am trying to make a list of consumed goods (fuel, water, etc...) in one month extracting data from DAILY report. It work, but only for one (this very) day. When make report for another day, the OLD data on the from cells on "monthly" list go to "zero value". Need to KEEP this data, or, beter say, I dont need any formula in cells from yesterday, enough is ONLY value. If I am not clear, please ask me to clarifie. Thank you. Attachment filename: formula.jpg Download attachment: http://www.excelforu...

Filtering Excel 2003 Charts to a date range
I have multiple charts that we look for trends on over 1-2 year time frames. Is it possible to have a chart show all the data that has been entered and when looking at the chart have a date range data to format that range differently. What I am trying to do is we have weekly or montly meetings, in these meetings I would like to show all the additional data differently that has been added since our last meeting, while showing accumulative data in a different format. The easiest way to get different formats is to use different series. If you had one series, you could use autofiltering to...

Multiple Cell Formats
Is it possible for a column to have more than one format, but for those formats to be similar. Eg. I would like to have a multi-format date column. The format can either be Month-Year (Jan-99) or Year only (1999). (I would even like to have something like Jan/Feb-99, but I can live without that.) Any help would be appreciated. Thanks. Hi With true Excel dates in column A e.g. 01 Feb 07, in column B =A1 Format>Cells>Number>Custom> mmm-yy will give Feb-07 or =TEXT(A1,"mmm-yy") =A1 Format>Cells>Number>Custom> yyyy will give 2007 or =TEXT(A1,"yy...

Line Color on Format Autoshape (arrows) won't hold
I'm using arrows from the Drawing toolbox and the color is on automatic and the arrow line keeps disappearing. I've changed it from automatic to a fixed color and it won't hold either. WTF? Any help will be appreciated. ...

highlight cell with colour if date overdue
can anyone tell me please how do i highlight a cell in a spreadsheet with colour if date overdue And how exactly should XL determine that the date was overdue? Take a look at Conditional Formatting in XL Help. If, for instance, the due date is in A1 and the date is overdue if it's in the past, select A1, choose Format/Conditional Formatting... and use something like: CF1: =A1<TODAY() Format1: <patterns>/<color> In article <3B7B11E7-894B-458A-BDE6-7710E0743D31@microsoft.com>, Harvey <Harvey@discussions.microsoft.com> wrote: > can anyone ...

Invalid Argument Error when using 1/1/08 as a date
Hi all, Not sure if this is a known issue - can anyone else create a record that contains a date field with the value of 1/1/08 or 1/1/07, save it and then open it again without getting an "Invalid Argument" error. I am able to produce this error across different environments, different entities and different date fields. Would be interesting to hear more about this. Thanks Hi there, Just to let you know, I just created a task in CRM 4 and set the due date to 1/1/08 and it worked without any issues. This is a field displayed as a Date/Time box, on not just Date. I don...

Formatting with CONCATENATE
I would like to use the TODAY() function with a leading word, in this case "Per ". The formula ="Per "&TODAY() produces "Per 38238" and I can't see any way to format the "38238" to make today's date readable. It's easy to do it by using 2 columns, one for "Per" and one for the date, but is there any way to do this in one column? Hi, Try some variation of the following: ="Per "&TEXT(TODAY(),"MM-DD-YY") >-----Original Message----- >I would like to use the TODAY() function with a leading word, in...

Conditional Formatting Text!!
Hi I want to conditionally format some text in a spreadsheet using formula: I have column A1 with: 4a Be 3a To 4c To 4b Be 4c Be 5c Be 5c To 6a Be etc... What I want to do is for all cells which contain "5a Be" "5b Be" 5c Be" "4a Be" "4b be" and "4c Be" to be highlighted in Red - is there a formula to put all of them into one formula - If("5a Be" or "5b Be) etc.. Thanks Kiran "Kiran" <kiran.vithal@gmail.com> wrote in message news:1143053337.964222.63000@t31g2000cwb.googlegroups.com... > Hi > ...

Date Formatting Problem #6
How can I convert the following date format into something useable that excel can recognize as a vaild date? Here is the date format I have to work with: Jan-01-05 Jan-02-05 , etc.... Any help would be much appreciated. Thank you! -- Jocko69 Hi One way Mark your range of data. Data>Text to Columns>Next>Next>Click Radio button for choose one of the date formats to suit. -- Regards Roger Govier "Jocko69" <Jocko69.1y2s0y@news.excelbanter.com> wrote in message news:Jocko69.1y2s0y@news.excelbanter.com... > > How can I convert the following date f...

Conditional formating using VBA
Hello I have to format cells (I4:J37) if cells (I43:J76) are: 100%-91% (green) 90%-76% (blue) 75%-50% (yellow) <50% (red) How do I write that in VBA? (I am new to VBA so any help would be appreciated) Many thanks Tracey Hi Tracey, I asume your users will enter a value in the range("I43:J76"). In that case you case use the Worksheet_change(Byval Target as Range function like so: Private Sub Worksheet_Change(ByVal Target As Range) ' Check if only 1 cells value is changed If Target.Cells.Count > 1 Then Exit Sub ' Check if changed c...

Converting current date to Julian Date
Does anyone know a simple function for converting the current date to a julian date. So Jan 1 would be 1 and Dec 31 would be 365 (except in leap year, of course). -- Edward Fultz Sr. Software Engineer Pamet Systems Acton, Ma www.pametsystems.com Thats not really julian date. But if you want the day of year, use COleDateTime::GetDayOfYear(). If you are really looking for julian date which is the number of days since January 1, 4713 BC and today is 2453416.14309 here is a site with a formula to calucate it. http://scienceworld.wolfram.com/astronomy/JulianDate.html AliR. "Ed Fultz&...