Default year in date entry

(Excel 2003)

When I enter e.g. 12/30 in a cell, Excel interprets that as the date 
2007-12-30, as expected.  But when I enter 1/6, Excel interprets that 
as last January, not next week.

Is there any way to tell Excel that dates before a certain date in 
the year should be interpreted as next year, not this year?

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work."  -- Marie Dressler, in /Dinner at Eight/
0
12/29/2007 12:38:59 PM
excel 39879 articles. 2 followers. Follow

3 Replies
429 Views

Similar Articles

[PageSpeed] 0

Stan,
Entering 1/6 can also result in 0.1666
Including the four digit year when entering dates is good practice.
The cell can be formatted to display what you want.
-- 
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Stan Brown" 
wrote in message 
(Excel 2003)
When I enter e.g. 12/30 in a cell, Excel interprets that as the date 
2007-12-30, as expected.  But when I enter 1/6, Excel interprets that 
as last January, not next week.

Is there any way to tell Excel that dates before a certain date in 
the year should be interpreted as next year, not this year?

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work."  -- Marie Dressler, in /Dinner at Eight/
0
jim.coneXXX (771)
12/29/2007 2:46:42 PM
If you enter something that looks like a date to excel, excel will see it as a
date.

If it needs to add the year to make it a real date, it'll use the current year.

So you can either enter the year, change the pc's system date to the year you
want (and change it back when you're done) or wait until next week to enter
those dates <bg>.

Stan Brown wrote:
> 
> (Excel 2003)
> 
> When I enter e.g. 12/30 in a cell, Excel interprets that as the date
> 2007-12-30, as expected.  But when I enter 1/6, Excel interprets that
> as last January, not next week.
> 
> Is there any way to tell Excel that dates before a certain date in
> the year should be interpreted as next year, not this year?
> 
> --
> Stan Brown, Oak Road Systems, Tompkins County, New York, USA
>                                   http://OakRoadSystems.com/
> "If there's one thing I know, it's men. I ought to: it's
> been my life work."  -- Marie Dressler, in /Dinner at Eight/

-- 

Dave Peterson
0
petersod (12005)
12/29/2007 2:51:09 PM
Sat, 29 Dec 2007 08:51:09 -0600 from Dave Peterson 
<petersod@verizonXSPAM.net>:
> Stan Brown wrote:
> > When I enter e.g. 12/30 in a cell, Excel interprets that as the date
> > 2007-12-30, as expected.  But when I enter 1/6, Excel interprets that
> > as last January, not next week.
> > 
> > Is there any way to tell Excel that dates before a certain date in
> > the year should be interpreted as next year, not this year?
> 
> If it needs to add the year to make it a real date, it'll use the
> current year.
> 
> So you can either enter the year, change the pc's system date to
> the year you want (and change it back when you're done) or wait
> until next week to enter those dates <bg>.

Thanks, Dave. That's what I was afraid of.

It's not a huge deal, but I was hoping here might be some sort of 
threshold, as I believe there is for deciding which century to use 
when a two-digit year is entered.

-- 
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
                                  http://OakRoadSystems.com/
"If there's one thing I know, it's men. I ought to: it's
been my life work."  -- Marie Dressler, in /Dinner at Eight/
0
12/29/2007 3:09:22 PM
Reply:

Similar Artilces:

getting data from date range
hi here is what I need. I need to run a query that picks the data i specify from this week's Monday back to and including the previous week's Monday. i need it to run this way in case the query is not run on Monday. Example: if I were to run it today, i would get data from 3/15/2010 back to and including 3/8/2010. If i run it tomorrow, I get the same data. Any suggestions would be helpful. Thanks On Thu, 18 Mar 2010 08:53:06 -0700 (PDT), pat67 <pbuscio@comcast.net> wrote: >hi here is what I need. I need to run a query that picks the data i >specify from this w...

data entry
I have 4 controls on a form which are used for data entry. How can I make sure they point to a new record when I open the form? At present, each of the controls displays the data from the last record Set the Data Entry property to Yes. It will open to a new record but you will not be able to edit existing records using this form. Alternative you can have On Open event to call a macro. Have that marco action go to new record. This means there will be a new record, even if you do not want one each time the form is opened. -- KARL DEWEY Build a little - Test a little "Rafi&q...

Month lookup with multiple years
I have a spreadsheet which derives project milestone dates. Within our company=92s process, there is a monthly meeting where the agenda deadline is the 1st Thursday of every month. In order to derive the first meeting that the project can be discussed, I have used the following formula: =3DWORKDAY(IF((AD15)>=3D((LOOKUP(MONTH(AD15),B69:F92,F69:F92))),(LOOKUP (MONTH(AD15)+1,B69:F92,F69:F92)),(LOOKUP(MONTH (AD15),B69:F92,F69:F92)))-1,1) Here, AD15 is the date where everything is ready for the meeting (which can be any time in the month), B69:F92 is a range of date- related columns* and F69:...

Fiscal Year settings and reporting
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C59F4A.30DC56F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi: I know that Fiscal year settings are once and for all. No changes can = take place in it. Is it possible to set the period to monthly and then = roll up by quarter? Can we generate quarterly reports if we select = month? =20 Thanks any help is appreciated.... --=20 Mansoor Awan=20 ------=_NextPart_000_0006_01C59F4A.30DC56F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfe...

Variance Total for less than complete year?
Hi, I'm comparing monthly data for the current year 2005 with monthly data for 2004 - easy-peasy for the month-on-month variance! My sheet has data for all 12 months of 2004, but I'm only filling in 2005 data as it becomes available. However, I want to show an accumulated total variance to date - i.e. the 7 month totals (jan-Jul2005) v. the corresponding 7 months totals for Jan-jul2004. How can I create a summing formula that adjusts accordingly? Many thanks, Enzo any ideas anyone? Enzo "Enzo" wrote: > Hi, > > I'm comparing monthly data for the cu...

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...

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...

change default site
Good morning, is it possible to change the program web site from the default site on 80 port to another port keeping the refer to the database? ...

third party driver catalog entries
Hi, I've added a couple of drivers to the public tree and to the catalog (public\mydriver\catalog\mydriver.pbcxml). I selected driver1 in the catalog and built and tested with it, then I unselected it and selected driver2, ran Build MyProjectname from the Build menu in PB (CE6R2). But driver1 keeps getting included in the build. I see that both driver1 and driver2 bib files are getting merged in and the dll's are both included in nk.bin. So it seems that adding to my image is easier than removing from the image. It's not just unchecking the catalog option and su...

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...

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 ...

Setting the default address book for all users?
Hi, I was just assigned the Exchange admin duties at our office. We have Exchange 2003 server on Windows 2003 Enterprise. Outlook XP (but moving to Outlook 2003 clients) Office management wants the default address book that displays first to be an Outlook address book instead of our Global Address book (our global address book contains email addresses of other outside offices and an email was accidently sent outside to someone with the same name). They want this done for ALL USERS. I know how to do it in Outlooks with the Tools->Address Book-> Options etc. Is there a way to set th...

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...

Auto-select combo box entry when only one row
I have 2 combo boxes the second is dependent on the first. When the first is clicked, I requery the second in the OnClick event. When the requeried combo box has only one row, I'd like this to be auto selected. How can I do this? Thanks. "mscertified" <rupert@tigerlily.com> wrote in message news:3AACB546-54A0-4BC2-8AFA-03AEB37F001E@microsoft.com... >I have 2 combo boxes the second is dependent on the first. When the first >is > clicked, I requery the second in the OnClick event. When the requeried > combo > box has only one row, I'd like this to be...

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...

Set sale price based on Sale start date and sale end date.
I want to change the sale price for items currently on sale by selecting the sale start date and sale end date in the Inventory wizard Task 110. Problem is that the filter option does not have these as options. How can I add these selections to the filter? The only way I've found to do it is to select a department, set the sale dates, delete all the items from the department off the worksheet, then add the items. I have a department set up with no items in it just for this purpose. Craig "LLoyd" <LLoyd@discussions.microsoft.com> wrote in message news:5B1CCBB3-1FCF-...

Need to Hard Code Date
Once the meeting date is typed in on the form (entered into the table) I don't want anyone to be able to change it. I've experimented with different options but they prevent puting in the meeting date on a new record. I'm sure the solution is simple, I just don't know what it is and I need to fix it by Monday. Thanks in advance! How about: Private Sub Form_Current() If IsNull(Me.MeetingDate) Then Me.MeetingDate.Locked = False Else Me.Meetingdate.Locked = True End If End Sub -- There's ALWAYS more than one way to skin a cat! Answers/posts based on...

between date text boxes will not link to subform
This is what I have done. I know it is not correct. I have added this to the 'Link Master Fields' property in a subform : Between [Forms]![SUB_ORDERS]![StartDate] And [Forms]![SUB_ORDERS]![EndDate] However, I cannot get the form to output on this criteria. Instead when I run this I get a pop up parameter with the exact code above printed on the popup. When I key in a date it goes straight to the form. I just wanted to use two text boxes with the between date functionality. Please tell me what I am doing wrong. Please. Thanks, Mitchell -- Message posted via AccessMonster.com http:...