Choice of Date or another field

I have a column which is used for component completion date.   The latest 
date is teh complition date for teh project.  I am using MAX(field:field) to 
select the latest date.  However, we are running across the occurance where a 
date is TBD(to be determined)  How can I choose the TBD if it is present in 
the column over the max date?
0
mark564 (750)
7/15/2005 7:13:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
540 Views

Similar Articles

[PageSpeed] 48

Hi!

Try this:

=IF(ISNUMBER(MATCH("TBD",B1:B30,0)),"TBD",MAX(B1:B30))

Format cell as DATE

Biff

"Mark" <Mark@discussions.microsoft.com> wrote in message 
news:7EAD2BA1-AACF-4921-B7EA-248C98E528F9@microsoft.com...
>I have a column which is used for component completion date.   The latest
> date is teh complition date for teh project.  I am using MAX(field:field) 
> to
> select the latest date.  However, we are running across the occurance 
> where a
> date is TBD(to be determined)  How can I choose the TBD if it is present 
> in
> the column over the max date? 


0
biffinpitt (3172)
7/16/2005 1:53:26 AM
Biff - 

Thanks for the help!!

Mark

"Biff" wrote:

> Hi!
> 
> Try this:
> 
> =IF(ISNUMBER(MATCH("TBD",B1:B30,0)),"TBD",MAX(B1:B30))
> 
> Format cell as DATE
> 
> Biff
> 
> "Mark" <Mark@discussions.microsoft.com> wrote in message 
> news:7EAD2BA1-AACF-4921-B7EA-248C98E528F9@microsoft.com...
> >I have a column which is used for component completion date.   The latest
> > date is teh complition date for teh project.  I am using MAX(field:field) 
> > to
> > select the latest date.  However, we are running across the occurance 
> > where a
> > date is TBD(to be determined)  How can I choose the TBD if it is present 
> > in
> > the column over the max date? 
> 
> 
> 
0
mark564 (750)
7/18/2005 12:31:07 PM
Reply:

Similar Artilces:

Expiry date for current version
I have been suing And which version is that? The general answer can be found in Help | About. "srn" <srinuapps@hotmail.com> wrote in message news:e63cO8nyJHA.5480@TK2MSFTNGP03.phx.gbl... >I have been suing ...

Removing auto date in charts
I want my chart to skip weekend dates. The data source does not contain weekends but the chart puts them in automatically. Help! ...

Question about ordering columns based on date
Hi, I have the following table and a slight ordering problem. Each of the rows A, B, C, etc has data associated with 5 fixed dates - from 20-Dec-09 to 20-Dec-18. In addition there is one "floating" column added to the end. In this example, it is the data for column 20-Dec-10. 20-Dec-09 20-Dec-11 20-Dec-13 20-Dec-15 20-Dec-18 20-Dec-10 A 84 49 88 91 26 82 B 9 68 39 89 50 59 C 45 5 46 69 41 38 D 35 28 70 69 47 59 The dates for the "floating" column always change - it could be any date before, after or in between the five fixed dates. I'd like to sort this data so that...

Cen an Excel file be set up to no longer open after a certain date????
Any help would be very much appreciated. Is there a way to set up an excel file to not open after a certain date or after a certain amount of time? Thank you in advance!! Lou Blah wrote: > Any help would be very much appreciated. > > Is there a way to set up an excel file to not open after a certain > date or after a certain amount of time? > > Thank you in advance!! > > Lou This could be done with a VBA Macro but you would have to take security measures to keep the user from tampering with the code. Maybe even incoporate code that deletes all of the data includ...

Pull value from another sheet
I have multiple sheets in my workbook. On the first sheet, I am pulling data from all the other sheets for a comparison. I need to pull in a commission level for specific group numbers. Here is what I need: Sheet 1 K3 is where my formula is. I need to match the value from sheet 1 B3 to sheet 2 I4. Then, if they match, I want the value from sheet 2 N4 to show up in sheet 1 K3. Another issue may be that on sheet two there may be multiple group numbers that are the same, however all commission levels for a group number are the same. So I guess I could go average or median if tha...

Date changing to current date when doc reopened
When I draft a letter and save it and then reopen it later, the date always changes to the current date, even when I do not use the auto insert date. i erase the date and put it in manually but it still happens. -- Michele Michelle, sounds like you used teh insert date feature of Word. This inserts a current field, which will update every time opened. Try replacing the current date field with teh Create date field. HTH "Michele" wrote: > When I draft a letter and save it and then reopen it later, the date always > changes to the current date, even when I ...

Forward to another SMTP address
We have our users in our domain XYZ.com with SMTP XYZ.com that wants their email forwarded to their other email accounts - like Yahoo, MSN, or other domains -- HOW do I setup the user's properties to do this? Thanks. Why not use OWA? Why forward to other email accounts? >-----Original Message----- >We have our users in our domain XYZ.com with SMTP XYZ.com that wants their >email forwarded to their other email accounts - like Yahoo, MSN, or other >domains -- HOW do I setup the user's properties to do this? Thanks. > > >. > "JD" <jd@mail.co...

Query to select from one table where no record in another table
I have a database where I keep various information about employees. One table "Employees" has their name and various other data. In another table "Accidents" I keep up with data relative to accidents they have had. This table has the date and other information about any accident an employee has had. I would like to recognize those employees who for a specified period of time (calendar year) have not had any accidents. I would appreciate your assistance in setting this up. Theres probably easiar ways to do it, but im not a professional. What I would do is crea...

Google Desktop index dates?
Just noticed - while trying to sort a problem with other user's Outlook not indexing after Dec16 - that on 'my side' (XP Pro) GD says my OE email was indexed up to July 2012! My pc definitely says this is Feb 2010 so what is going on? (Nobody seems to be answering postings on 'google desktop/somethingsbroken') Cheers, S I think you can uninstall it via Add / Remove Programs. steve "spamlet" <spam.morespam@invalid.invalid> wrote in message news:eBd%23TFCpKHA.3948@TK2MSFTNGP06.phx.gbl... > Just noticed - while trying to sort a pr...

Create Formula
I need to create a formula where I add a set number of Networkdays to a start date. Example: Start Date = 1/2/07 Number of Networkdays = 21 End Date = Is calculated If Start = 1/2/07 and 21 Networkdays are added, what is the retured end date???? I can only find example of Networkdays where I would be providing the Start and End Dates, and it will calculate the Networkdays. I can't find example where End Date is calculated based on the number of networkdays from start date. Can anyone help with a valid formula for this? Thanks!! Look for =workday() in Excel's help. (Also part o...

how to alter the date within a macro
Hello, I was trying to create an archive function on a workbook. I have a formula throughout the workbook that is based on the current date using NOW(). I went in a recorded a macro of replacing the NOW() with last years date. How can I set this so that I can have the user push a button to archive the sheet and have the year based on the current date? I want to change the replacement to an argument that gives the year -1 at the time of clicking the archive button. The current recorded code follows. Thank You LWhite Cells.Replace What:="NOW()", Replacement:="Date (...

Linking computer dates (time) to spreadsheet dates that have formu
Aloha I am hoping someone is out there who can assist me. I have spent two days trying to do two simple things in a medicine sheet I made up. It involves the dates. They need to change by the month, we have figured out the formulas that in itself was difficult. But NOW HOW do I TIE IN my computers Calendar (where the time clock) is TO the dates I have in the spreadsheet? So when the computer calendar changes the spreadsheet dates change per the formula that is in there? THank you very much Antoinette We probably need a bit more information on what you are trying to do, but ...

days calculation between dates
I would like to calculate the days based on three conditions. Here is the example of my work sheet: A B C D E F 1 Start date finish date Jan Feb Mar April 2 Jan 03, 2009 Jan 27, 2009 3 Feb 24, 2009 4 Feb 25, 2009 Mar 12, 2009 I want see the days caclulated by month for any task started. If task goes to next month, days should go to next month column and I want to calculate the weekdays only. As above row 3 date in col...

Expired date to show up automatically
In our company we have safety harnesses and they need to be recertified every year. I Would like to put the date in and then for it to show up as expired in a year with a different colour. There must be a way to do this with a formula or something? Thanks Use conditional formatting>Cell vale>Less than and enter =DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())) -- Ian -- "renate" <renate@discussions.microsoft.com> wrote in message news:AB15DB4B-53E2-4CC1-B8C0-300F6008774B@microsoft.com... > In our company we have safety harnesses and they need to be recertifie...

Change Calender Start date
I want to add this to my code If [ckb7] = -1 Me!tbDateFrom = DateSerial(Year(Date), Month(Date) - 1, 7) Is this possible?.....Thanks for any Help............Bob Private Sub Form_Open(Cancel As Integer) Select Case Me.OpenArgs Case "OwnerStatement" Me.Caption = "Owner Statement" tbDateFrom.value = Format("01-" & Month(Now) & "-" & Year(Now), "dd-mmm-yyyy") tbDateTo.value = Format(Now, "dd-mmm-yyyy") ...

Month End Date Ticks
Hello, In Excel 2003 I could use daily data starting on, say, 31 Dec 2005 and ending some years later for a line chart. If in the x-axis settings I selected both Major and Minor units to 3 Months with Base units as Days I would get ticks at each calender quarter end. In this example; 31 Dec 2005, 31 Mar 2006, 30 Jun 2006, and so on. However, in Excel 2007 the axis switches over to start of month ticks; 31 Dec 2005, 31 Mar 2006, 1 Jul 2006, and so on. Does anyone know what is going on with the date x-axis in Excel 2007? It seems to be a bug as I have tried all manner of settings an...

How do I insert the date the file was saved in the MS Excel foote.
I am trying to display the date the file was last saved in the footer field. the defualt date code displays the date the file was printed. I would appreciate any help with this. Hi kacate Try http://www.rondebruin.nl/print.htm#Saved -- Regards Ron de Bruin http://www.rondebruin.nl "kacate" <kacate@discussions.microsoft.com> wrote in message news:9F65885A-DC2D-4515-A0D2-5E39B7905FCF@microsoft.com... >I am trying to display the date the file was last saved in the footer field. > the defualt date code displays the date the file was printed. I would > apprec...

fields in the RecordSource
I get a runtime 2465 error in attempting to reference one of the fields in the RecordSource of a report. E.g., Dim intCount As Integer intCount = Me.CountField Access can't find the referenced field, "CountField". Yet, using the "Me." autofill shows all the field names in the RecordSource of the report. What am I missing? Bill Which event is being used to run that code? If it's the Open event, the report's fields are not available to the VBA code at that time. You more likely want to use the Format or Print event of a section of the report that might...

Copy and Paste in Notes or Fields
In GP Version 8, we currently cannot copy and paste any field. When I have a lengthy description for several invoices or for several notes, I have to re-type each one. That would be a time saver. I mean, we've been able to cut and paste in Word and Excel for many years ... ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsrea...

Open worksheet by date
I have a workbook which contains twelve worksheets - January, February............., December. I would like to have the active worksheet upon opening correspond to the current month. E.g. if the workbook is opened in March then the "March" worksheet displays. How can I do this? Sandy Sandy, Copy the code below and paste it into the Thisworkbook object's codemodule. HTH, Bernie MS Excel MVP Private Sub Workbook_Open() On Error Resume Next Worksheets(Format(Date, "mmmm")).Activate End Sub "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in messa...

security setup for user class copied to another company
We have 3 companies in GP- version 9 - we have a user class set up and want to copy the security settings to the other companies. I was told it could be done through advanced security - is that correct? Do we use the import/export function? Thanks. User Classes are a system wide setting. Once you assign the user to the class, and grant the user access to a company, their security will follow them to that company. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.bl...

Calculated Fields in Pivot Tables
Hello from Vancouver BC! I have a pivot table into which I have inserted calculated fields. I totalling paid insurance claims by month, grouped by year. The calculated field mutiplies the paid claims times an expense fee. The monthly calculation is working fine, but the subtotal for the year is a mystery. I though it would simply add the sum of the monthly amounts like the uncalculated fields. Can anyone assist? Thanks very much, Shane Can you give an example of an amount you're getting as an annual total, and what amount you expected to get? Shane wrote: > Hello from Vancouve...

Formula to reference another cell in a worksheet
Column G is filled with numbers which represent Rows in my worksheet. I want Column H to equal the contents of Column A Row ? which is referenced in Column G. Example: G1 is 1043, I want H1 to be equal to A1043. What formula can I use to fill column F to do this automatically. Thank you! Heather =indirect("A" & G1) -- HTH... Jim Thomlinson "HeatherJ" wrote: > Column G is filled with numbers which represent Rows in my worksheet. I want > Column H to equal the contents of Column A Row ? which is referenced in > Column G. >...

Entering early dates such as 3100 B.C.
How do I start the timeline at 3100 BC ? It will not accept any year before 1899. "angmont" <angmont@discussions.microsoft.com> wrote in message news:4666A577-0B63-4005-BE45-B9EC7731888C@microsoft.com... > How do I start the timeline at 3100 BC ? It will not accept any year > before > 1899. Sorry, the timeline uses the OS dates and the earliest date is around 1900 slightly after 3100BC. You could enter the dates as text, by selecting the timeline, do Open Group ( last item on the Edit menu) selecting the date text and overtyping with the new dat...

Using variable in field name
Hi. I have a table whose fields are named boolType00, boolType01, boolType02, ..., boolType19. I need to make a loop and real all of the 20 fields. Something like: Dim i1 As Integer i1=0 While i1 < 20 Debug.Print MyTable!boolType"i1" i1=i1+1 Wend Is there a way to do it? Thanks H. Martins If you OpenRecordset, you can refer to the field in the recordset like this: Debug.Print rs.Fields("boolType" & i1) If you need help with using a recordset, see: http://allenbrowne.com/func-DAO.html#DAORecordsetExample Lots of repeating fields like that usua...