Default Dates

I have a worksheet in Excel 2003 that has 2 columns with dates paste linked 
from another workbook.  One is a start date and the other a finish date.  I 
want excel to automatically default any start date to 1/04/05 where that date 
is before 1/04/05, and in the finish date column, to automatically default 
the date to 31/03/06 for any dates that are after 31/03/06 or there is no 
date at all for that column.  I don't want the default dates to alter any 
dates that fall from 1/04/05 to 31/03/06 because they will be relevant dates 
I need to keep.  Can this be done?
0
sue (191)
7/22/2005 11:21:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
702 Views

Similar Articles

[PageSpeed] 54

You can use something similar to:

if(link<startdate,startdate,if(link>finishdate,finishdate,link))

-- 
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com


"Sue" <Sue@discussions.microsoft.com> wrote in message 
news:CECA579F-26A9-478C-8689-DBEC6E1A7D8B@microsoft.com...
>I have a worksheet in Excel 2003 that has 2 columns with dates paste linked
> from another workbook.  One is a start date and the other a finish date. 
> I
> want excel to automatically default any start date to 1/04/05 where that 
> date
> is before 1/04/05, and in the finish date column, to automatically default
> the date to 31/03/06 for any dates that are after 31/03/06 or there is no
> date at all for that column.  I don't want the default dates to alter any
> dates that fall from 1/04/05 to 31/03/06 because they will be relevant 
> dates
> I need to keep.  Can this be done? 


0
dlongwth (59)
7/22/2005 11:29:02 AM
Reply:

Similar Artilces:

How to change the default value in "Filter On" field (Microsoft CRM 3.0)
Hi. When I open a contact in CRM, I can view the sales activities (Opportunities, Quotes, Orders, and Invoices) under "Sales" session. Is there any way I can change the default value under "Filter On" drop down menu? For example, when I click on Invoice, I want "Filter on:" drop down list select "Paid" instead of "Active" Thanks for your advice. You do the following: Settings -> Customizations -> Customize Entities Select the contact entity Then you will see a list of views and a form. One of those views is set ...

Help with returning records given a date range
Hi there, I was wondering if anyone could give me some pointers as to where to start with the following? I have a spreadsheet of data in a table. One of the fields is date. I would like the user to be able to enter a date range and have all the records returned that fall within that range. What function should I use for that? DGET can only return one record. Could I use something like VLOOKUP in an array? The number of records returned will obviously change depending on the date range entered. Or, should I use something like a VBA loop? I need to do this in Excel - it can't be in Acce...

Changing default indents for lists (Word 2007)
In previous versions of Word this was relatively easy. I'm pretty adept with Office products, but as far as I can see this is no longer possible. Can someone confirm or enlighten me? I don't like the indents that Word 2007 imposes on bulleted and numbered lists -- it wastes a lt of space and for me is difficult to visually scan. I prefer my first levels of bullets (or numbers) to appear so that the bullet is lined up with the left margin with a hanging indent of 0.25". Then I like the second level of bullets to be at 0.25" with a hanging indent of 0.5", ...

Date Entry Question
I want to enter JUST the month and year and have Excel recognize it as such. Unfortunately, Excel decides the year I am entering is a day of a month and uses the current year to complete the date. For example, I enter Feb-03 into a date-formatted cell (Mmm-yy) and Excel shows Feb-04 as the output. If I click on the cell, the value is 2/3/2004. Is there anyway around this? Excel will always treat a date as mm/dd/yyyy format (at least in the U.S.). So if you enter 02/2004, Excel will show 2/1/2004 in the formula bar. The only thing you can do (I think) is format the cell as mm/yyyy o...

cannot open a table by todays date
Hi Can anyone point me in the right direction here, I would like to open a table in access 2003 by date. I have an asp web page which needs to read data from a table with each days today's date (which ever day that is) then a new table is created with today's date. Example: I have a table called 17-may-2007 my ASP page reads this table for 24hours then tomorrow (12:00 midnight 18th) I will have a new table called 18-may-2007 and the old table is left behind (As 17-may-2007) so I need my sql statement to automatically open table by today's date. This is what I have so far but it...

Default Weight in Pivot Table Charts keeps getting reset
Hi, I have created a Pivot Table chart and I am using basic line chart to display the data...I would like to make the line weight 2 points heavier than the standard but each time I select new values from the Pivot Table chart it resets to the default....where do you set this to remain at the values entered... Any help appreciated.... Scott ...

Default date for transactions
I used to have a checkbook program where you could set the default date for new transactions to yesterday. This was very convenient for me...since I hardly ever recorded checkbook activity on the same day that I went out and spent the money. Is that option available in Money? Have a nice week... Trent Fighting for peace is like screwing for virginity! Nope. But it will keep using the date you last used, so entering three transactions in a row for yesterday means only having to enter yesterday's date once. <Trent�> wrote in message news:mifcuvo4lch1lmvvj3m62hl4jbefhcqn23@4ax.c...

Default font in dialog property pages
Have to post this again as all attempts to resolve the issue on my own were unsuccessful. The problem is, despite a property page template of a property sheet explicitly indicates that another font than the default MS Sans Serif 8 must be used (e.g. Arial 10), this never comes into effect. I checked the resource file to ensure that appropriate instructions are presented in there: > > IDD_WIZ_PAGE0 DIALOG DISCARDABLE 0, 0, 235, 156 > > STYLE WS_CHILD | WS_DISABLED | WS_CAPTION > > CAPTION "Welcome to the Operation Wizard" > > FONT 10, "Arial"...

formula to calculate # of days between dates, excluding holidays
I am wondering if there is a formula out there that will calculate the number of days between two dates, but exlclude holidays. I know there is a formula that will calculate work days, but I need one to calculate all days of the week, not just work days. Assuming your dates are in A1 and B1, Create a RangeName called Holidays in an out of the way place and list your holidays there........then use =(B1-A1)-COUNT(Holidays) Vaya con Dios, Chuck, CABGx3 "abs2299" <abs2299@discussions.microsoft.com> wrote in message news:01F99198-5398-416E-8C3F-2179CF6ADC0E@microsoft.com.....

where is the install for default text box add-in
Maybe you mean Default Text Box v1.0.0.3 http://ed.mvps.org/Static.aspx?=Publisher/add-ins Normal.pub? http://ed.mvps.org/Static.aspx?=Publisher/normal.pub -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "lpyburn" <lpyburn@discussions.microsoft.com> wrote in message news:692EA943-DB76-474F-B4C4-CA0222EBA04D@microsoft.com... > ...

excel date ?
Dear All, I don't know how to do a cell convertion as below: Let say input 140709 into a cell and then press Enter or Tab Key. I want the cell auto change to 14/07/09 after pressed Enter or Tab key. Did anyone know how to do it automatically ? Many thanks for help. William Chan See this: http://www.cpearson.com/Excel/DateTimeEntry.htm -- Biff Microsoft Excel MVP "��H@work" <fatguy@fatguy.com> wrote in message news:uy%23J3tDBKHA.4432@TK2MSFTNGP05.phx.gbl... > Dear All, > > I don't know how to do a cell convertion as below: > > Let say in...

Search feature is needed in by date, name, id, telnumber, ...
hi Search feature is needed in by date, name, id, telnumber, in SDI Application. from file. I want to implement search feature in my application... and display those things ... Thank u in advance... Read file, compare string, when you find it display it. I would have been able to help out more if I hadn't skipped that mind reading class in college. :) AliR. "sargio" <naveenee20@gmail.com> wrote in message news:1136303289.450386.248270@g49g2000cwa.googlegroups.com... > hi > > Search feature is needed in by date, name, id, telnumber, in SDI > Application. ...

Formating Last Year's Date
I need to enter a lot of 2005 dates. Is there any way to make the default date format use 2005 instead of 2006? I could always enter the year aver time I enter a date, and/or I could change Windows Date/Time to some fictitious date in 2005, but I'd rather not do either of these. Thanks Dave If you enter 31/1 (without the year), then Excel will assume that you mean the current year and will make this 31/01/2006 (maybe formatted differently for you). If you subsequently subtract 365 from this you will get 31/01/2005, so here's what to do: enter your dates in the short form ig...

Help with Date Range Formula
I am helping with a spreadsheet and have had little success in trying to come up with a solution. Here's the problem. I will need the ability to have the formula reference a range of dates based on a max for each date of incident and return and answer. Here's and an example: Lets say I have 3 entries Name date of incident Average Monthly Wage John Doe 01/01/2008 4333. Jane Doe 01/01/2003 2888 Sally Doe 01/01/2009 3600 In the example above I would need to have the average monthly wage col...

Date field
I have a date field which has a range of dates from 1850 - 1930 Each entry has dd mm yyyy how do I 1) format all fields to read e.g. dd Mmm yyyy 2) sort successfully on all records Bob M Select the range you want to format. Right click. In the Number Tab you will find the field "date" and on the right side you will find the format you need. Hope it helps. "Bob Matthews" wrote: > I have a date field which has a range of dates from 1850 - 1930 > Each entry has dd mm yyyy > how do I > 1) format all fields to read e.g. dd Mmm yyyy > 2) sort successfully ...

Signature with Date or Time included ? Outlook 2003
Is there a way to have the signature function in Outlook 2003 also insert a date or time (or both) field ? The only way I saw was to call a file that checked the date/time and that seems rather awkward. Thanks ! Tedd -- Tedd Riggs PDA Square Content Developer www.pdasquare.com You could probably program a little routine that inserts your signature and appends the time after checking the system clock. Check with the Outlook programming groups to see if you can get a head start or if someone has already written something. --� Milly Staples [MVP - Outlook] Post all replies to the group ...

Wrong Posting Date
today I changed the system date to 7/31/05 and created a bunch of returns. I applied all of those returns to outstanding invoices and then posted everything through the G/L. As it turns out those entries should have been made in August. What is the correct way to reflect those returns to the correct date? Do you have SQL? One way is too create invoices for them all in July and redo for August. If you have Sql you could use a date change script for just those transactions "Brian" wrote: > today I changed the system date to 7/31/05 and created a bunch of returns. I > ...

Created Date in SmartList
Hello: Could someone please tell me what the Created Date is, in the SmartList Account Transactions object in GP 9.0? The Created Date, if it is supposted to be the date of creation of the transaction from the subledger or the resulting posted journal entry, is inaccurate. On my SmartList here on my laptop, it is showing as August for transactions that are dated in March. And, at a client of ours, it is showing February for transactions that are dated in August. TK on PartnerSource says that inaccuracies in these dates represent a problem report that has not been fixed. Thanks! ch...

graphing dates
I'm trying to graph these dates; 9/1 9/5 9/6 But when I do this, 9/2, 9/3, and 9/4 are showing up on the chart. I don't have data for these dates so I don't want to include them. Why is it automatically entering?? Hi, The chart wizard is trying to be helpful by assuming you want a time series axis for your dates. If you don't and you actually want just categories then right click the chart and pick chart options... On the axis tab set the scale to be category rather than automatic. Cheers Andy dw9050 wrote: > I'm trying to graph these dates; > 9/1 > 9/5 &...

Calculating a week between a range of dates
Hi, I have a little table which looks up a period number, and also has 4 columns for the week numbers of the period. Example Period week 1 week 2 week 3 week4 P1 05/04/10 12/04/10 19/04/10 26/04/10 P2 03/05/10 10/05/10 17/05/10 24/05/10 I have worked out a simple formala, to say if todays date is 13/04/10, it will return period P1. (as its between 05/04/10 - 03/05/10) What i am trying to do, is to now work out the week number , which should be week 2 (as its between 12/04/10 - 19/04/10) Any help fully appriciated. Thanks How ...

Default output confusion
I've two sound cards in my PC ("A" and "B"). "A" is the system wide default device and have configured WMP (12.0.7600,16415) to play through "B" vie WMP->tools->options->devices->Speakers. This works for mp3 & wmv music stored in the disk but WMP persists in playing streamed radio stations through "A". Is there a way to sort this whereby WMP plays all its output through "B"? ...

have dates entered from a list of data into an excel template
I want to create a calendar (I downloaded the 2005 template). I have a list of dates with an adjacent list of "to dos". How can I automatically have the dates inserted into the appropriate calendar day? Chip Pearson has just such a calendar. No need to re-invent the wheel. http://www.cpearson.com/excel/download.htm -- Don Guillett SalesAid Software donaldb@281.com "Diane" <Diane@discussions.microsoft.com> wrote in message news:32E56745-4E4B-419F-9692-9680E7E5C3DA@microsoft.com... > I want to create a calendar (I downloaded the 2005 template). I have a list >...

conditional format -- if not a date, then highlight cell
How do I enter a conditional format to highlight the cell if it's not a valid date? I tried "formula is": =if(not isdate(C10)) <>if(isdate(C10)) Your help would be greatly appreciated!! I think I'd put some data validation on the field to ensure that it's a date as well. -- HTH, Barb Reinhardt "laavista" wrote: > How do I enter a conditional format to highlight the cell if it's not a valid > date? > > I tried "formula is": > > =if(not isdate(C10)) > <>if(isdate(C10)) > ...

Change date in Notes?
When I create an Entourage Note, Entourage creates a DATE entry, based on the date the item was created in Entourage. Is it possible to change this date? And if not - if it can only be the date the note was actually cretaed (or editted) - is there some way I can create a CUSTOM column into which I could place a date of my choice? On 6/17/05 2:39 PM, in article #70zdU4cFHA.1288@tk2msftngp13.phx.gbl, "T. Wise" <terwise111@yahoo.com> wrote: > When I create an Entourage Note, Entourage creates a DATE entry, based > on the date the item was created in Entourage. I...

Graph and Input box for Date Range
All, This is just my thought but have no idea how to proceed. Can someone please provide a solution, I will really appreciate. Not sure how to attach a spreadsheet by my data spreadsheet is uploaded to File section known as DATA.XLSX. Col A. contains dates ( 1-Jan to 16-Aug ) and Col B-K contains data starting year 2001-2010 for the same dates. I would like to create a Date Range Input box so whenever I specify date ranges, it automatically pulls data of those dates from 2001 to 2010 and creates some type of line chart. E.g I specify date1: 3-march and date2: 10-June then line graph sho...