#### Date Formulas #3

```How can I make a cell correct the year of a date
```
 0
charlene (12)
1/6/2005 3:29:06 PM
excel.misc 78881 articles. 5 followers.

7 Replies
425 Views

Similar Articles

[PageSpeed] 25

```Hi Charlene

not really sure what you're after here
if you have
1/1/05
and you want to display just
2005
then right mouse click on the cell and choose format cells / on the numbers
tab, choose custom and then type
yyyy
in the white line and and click OK.

however, this just displays the date showing only the year, if you want to
extract the year only to another cell you can use
=year(A1)
where A1 contains the date

if you're after something else, please type a few examples of the data you
have and what you want to see.

Cheers
JulieD

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
> How can I make a cell correct the year of a date

```
 0
JulieD1 (2295)
1/6/2005 3:32:22 PM
```Hi

With the date of your birth in cell A1, this formula in another cell will
return this year's birthday:
=DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

HTH. best wishes Harald

"Charlene" <Charlene@discussions.microsoft.com> skrev i melding
news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
> How can I make a cell correct the year of a date

```
 0
stf (176)
1/6/2005 3:33:21 PM
```thank you for your reply Harald, but I don't think I explained myself very
well.

My employees use Excel to enter timesheet info.  If they accidentially enter
the year 2004 instead of 2005, is there a way Excel can automatically change
the year to 2005?

"Harald Staff" wrote:

> Hi
>
> With the date of your birth in cell A1, this formula in another cell will
> return this year's birthday:
> =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
>
> HTH. best wishes Harald
>
> "Charlene" <Charlene@discussions.microsoft.com> skrev i melding
> news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
> > How can I make a cell correct the year of a date
>
>
>
```
 0
charlene (12)
1/6/2005 4:13:07 PM
```Hi Charlene

what cell(s) are they typing the date into in the timesheet structure?

Cheers
JulieD

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
> thank you for your reply Harald, but I don't think I explained myself very
> well.
>
> My employees use Excel to enter timesheet info.  If they accidentially
> enter
> the year 2004 instead of 2005, is there a way Excel can automatically
> change
> the year to 2005?
>
> "Harald Staff" wrote:
>
>> Hi
>>
>> With the date of your birth in cell A1, this formula in another cell will
>> return this year's birthday:
>> =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
>>
>> HTH. best wishes Harald
>>
>> "Charlene" <Charlene@discussions.microsoft.com> skrev i melding
>> news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
>> > How can I make a cell correct the year of a date
>>
>>
>>

```
 0
JulieD1 (2295)
1/6/2005 4:15:57 PM
```Use data > validation to restrict the entry to dates greater than or equal
to 1/1/2005.

Carlos

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
> thank you for your reply Harald, but I don't think I explained myself very
> well.
>
> My employees use Excel to enter timesheet info.  If they accidentially
enter
> the year 2004 instead of 2005, is there a way Excel can automatically
change
> the year to 2005?
>
> "Harald Staff" wrote:
>
> > Hi
> >
> > With the date of your birth in cell A1, this formula in another cell
will
> > return this year's birthday:
> > =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
> >
> > HTH. best wishes Harald
> >
> > "Charlene" <Charlene@discussions.microsoft.com> skrev i melding
> > news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
> > > How can I make a cell correct the year of a date
> >
> >
> >

```
 0
nunayo (95)
1/6/2005 4:39:12 PM
```Carlos, thank you, that is exactly what I want to do, but I don't know how to
do it.  Can you help?

"CarlosAntenna" wrote:

> Use data > validation to restrict the entry to dates greater than or equal
> to 1/1/2005.
>
> Carlos
>
> "Charlene" <Charlene@discussions.microsoft.com> wrote in message
> > thank you for your reply Harald, but I don't think I explained myself very
> > well.
> >
> > My employees use Excel to enter timesheet info.  If they accidentially
> enter
> > the year 2004 instead of 2005, is there a way Excel can automatically
> change
> > the year to 2005?
> >
> > "Harald Staff" wrote:
> >
> > > Hi
> > >
> > > With the date of your birth in cell A1, this formula in another cell
> will
> > > return this year's birthday:
> > > =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
> > >
> > > HTH. best wishes Harald
> > >
> > > "Charlene" <Charlene@discussions.microsoft.com> skrev i melding
> > > news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
> > > > How can I make a cell correct the year of a date
> > >
> > >
> > >
>
>
>
```
 0
charlene (12)
1/6/2005 7:47:04 PM
```1) Select the cell or range of cells you want to restrict.
2) Pull down the Data menu.
3) Choose Validation...
4) Settings Tab, Allow: Date, Data: Greater than or equal to , Start Date:
1/1/2005
5) Error Alert Tab, Error message: It's 2005, Dummy.
6) OK button
7) Check behavior by entering good and bad dates.

Carlos

"Charlene" <Charlene@discussions.microsoft.com> wrote in message
news:E3A1A7DB-0F13-41F0-9DAC-9837BBDC2C77@microsoft.com...
> Carlos, thank you, that is exactly what I want to do, but I don't know how
> to
> do it.  Can you help?
>
> "CarlosAntenna" wrote:
>
>> Use data > validation to restrict the entry to dates greater than or
>> equal
>> to 1/1/2005.
>>
>> Carlos
>>
>> "Charlene" <Charlene@discussions.microsoft.com> wrote in message
>> > thank you for your reply Harald, but I don't think I explained myself
>> > very
>> > well.
>> >
>> > My employees use Excel to enter timesheet info.  If they accidentially
>> enter
>> > the year 2004 instead of 2005, is there a way Excel can automatically
>> change
>> > the year to 2005?
>> >
>> > "Harald Staff" wrote:
>> >
>> > > Hi
>> > >
>> > > With the date of your birth in cell A1, this formula in another cell
>> will
>> > > return this year's birthday:
>> > > =DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))
>> > >
>> > > HTH. best wishes Harald
>> > >
>> > > "Charlene" <Charlene@discussions.microsoft.com> skrev i melding
>> > > news:314724C0-63FE-491F-80BA-0213D346A7AE@microsoft.com...
>> > > > How can I make a cell correct the year of a date
>> > >
>> > >
>> > >
>>
>>
>>

```
 0
1/6/2005 11:46:55 PM

Similar Artilces:

CRM 3.0 Implementation
I am interested in the experiences of others with implementing Microsoft CRM 3.0. I am a one man development team who has been tasked with implementing CRM 3.0 with 30 users initially. Our organization has been running on Lotus Notes for quite a while. We moved to echange for e-mail over a year ago but still use Lotus for custom databases. The first step will be pulling the data from Lotus Notes to CRM. I have looked into the Microsoft CRM 3.0 Certification. There is a company that offers a 10 day CRM 3.0 boot camp. Is this a good idea, and at what point should I take it? We would lik...

Oldest date for Duplicate Cust. #
I'm trying to get the oldest date associated with a customer number, and in the Cust# column, i'll have many duplications of the same customer number. Let's say A is "Date", and B is "Cust#". (I won't be able to allow my users to sort the data, so i'll need a formula that returns either the oldest date, or the cell which contains the oldest date.) Any help is much appreciated! Nevermind. I found it using Google/Groups. {=MIN(IF(\$B\$1:\$B\$10=B1,\$A\$1:\$A\$10))} >-----Original Message----- >I'm trying to get the oldest date associated with a...

crm 3.0 error 03-01-06
Hello, I'me getting this error while installing crm3.0 for SBS: "error writing to file microsoft.mshtml.dll verify that you have access to that directory" That file is in the C:\Program Files\Microsoft.NET\Primary Interop Assemblies directory. I (and 'everyone') has full access to that dir. What can I do about this?? kind regards, Thomas ...

Dates #9
The problem of a date code... I need to address this so that fo example, 5/6/04 can be correctly entered as either 5th of June or 6t of May, depending from where the date emanted. regards -- Message posted from http://www.ExcelForum.com Couldn't you format the cell as mmmm dd, yyyy so that the user sees what date they entered in a non-ambiguous manner right away? Or maybe provide 3 inputs: Month, day, and year. You could combine them elsewhere. "adn4n <" wrote: > > The problem of a date code... I need to address this so that for > example, 5/6/04 can be c...

Joining text with a formula in cell #4
just to complete the thread... I found the answer. You have to change the format of the cell to custom 0.00"*" this is the only way it will show only 2 decimal places Thanks for the hel -- Mustard Hea ----------------------------------------------------------------------- Mustard Head's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1630 View this thread: http://www.excelforum.com/showthread.php?threadid=27700 ...

Office 2003 Service Pack 3--subsequent problems opening Publisher
I run Publisher 2003 on Windows XP. On June 13, I updated my system with Office 2003 Service Pack 3 so that I could open Word documents with the file ext docx. Subsequent to the Service Pack 3 installation, whenever I open a Publisher file (which I created), I get the following message: "Publisher has detected a problem in the file you are trying to open. If you are certain that this file came from a trusted source and does not contain harmful information, click OK." What is causing this and is there a way to stop this pop-up message? All publications? Error message when you...

Qrp Date functions
Where do I find the various functions to modify the Reports like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others and what they mean???? Barry Found the information at MSDN Transact-SQL Reference Barry "Barry L" <barryl@eryanjewelers.com> wrote in message news:usd3uP1CIHA.1188@TK2MSFTNGP04.phx.gbl... > Where do I find the various functions to modify the Reports > like cast(DatePart(Dd,[Transaction].Time) as nvarchar) and others > and what they mean???? > > Barry > ...

Extending formulas
Subject: Extending formulas Hi, For my application that uses Excel for calculations. I need to be able to extend the forula base of Excell with complex scientifc functions. Is there a way to add new functions to the Excel function base? Thanks Spx. MS has provided Visual Basic for Applications (VBA) to customize Excel with new functions, commands, forms, menus, etc. Tools|Macro|Visual Basic Editor From the VBA editor Insert Module Then write your functions in VBA. Details of writting functions in VBA is a very big topic, http://www.fontstuff.com/vba/vbatut01.htm may help y...

Some specific users are constantly getting prompted for CRM login in Outlook. We are using desktop client (online) online. This happens randomly. We have CRM 3.0 with rollup update 2 and IE7. We have also applied this fix http://support.microsoft.com/default.aspx/kb/934243/en-us. Also added the crm site to local intranet zone. Please help. Thanks. set the authentication in IE check rollup update 2 -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "raj" wrote: >...

How do you get rid of the seconds in the time area. I have changed the formatting in the time. I use the excel file as a data source. I include the time in the mail merge. It always shows up with the seconds in the time. Very frustrating. HELP PLEASE!! TJ it may be formatted as text, so won't respond to changing the time format. if it is text, the TIMEVALUE formula will convert it to a decimal-based time value which can then be formatted by using Excel's normal Number formatting-- to get rid of the seconds. Eddie O "TJ" wrote: > How do you get rid of the secon...

I've got two workbooks on a shared drive with hyperlinks linking the two. When a user clicks on the hyperlink on the first workbook, it takes him to the second workbook. Fine. However, when the user clicks on the hyperlink in the second workbook to go back to the first, the error message says that that workbook is already open and it cannot open two files with the same name. Help is appreciated! I just tried a small test in xl2002 and it worked ok for me. I use Insert|Hyperlink to create the links. Are you sure that the hyperlinks point at the file you want--same folder and e...

date tracking
I am entering clients into a 2007 Excel spreadsheet. How do I make the entry turn color when 14 days have passed? Gene This is a multi-part message in MIME format. ------=_NextPart_000_0018_01CAC8D4.5688AC60 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit As part of the "client" entries, do you enter the date the client was entered? This would be the key in doing this task. In a cell on the worksheet you could enter the formula for today's date like this =TODAY(). Then use the con...

Outlook Client #3
Dear All, I have recently installed crm outlook client for one of my users and then also installed the 2 rollups for version 3.0 . Unfortunately outlook is still restarting even after the rollups. Kidnly advise the necessary solution that resolve the problem. Please clarify, outlook loads and then crash "restart"? Has Office applied with latest Office update? Frank Lee, Microsoft Dynamics CRM MVP http://microsoft-crm.spaces.live.com http://www.workopia.com/Links.htm "Faiz Amir" wrote: > Dear All, > I have recently installed crm outlook client for one of my ...

Rounding Numbers #3
I have a list of values as below: 476.14 361.99 345.69 463.08 515.29 403.44 330.68 347.64 375.36 I would like to create a formula that rounds the values to the nearest 0.05 eg. Round 476.14 to 476.15, 361.99 to 362.00, 375.36 to 375.35 etcâ€¦ Is there anyway that I can do this? Thanks, Jane. JaneC wrote: > I have a list of values as below: > 476.14 > 361.99 > 345.69 > 463.08 > 515.29 > 403.44 > 330.68 > 347.64 > 375.36 > I would like to create a formula that rounds the values > to the nearest 0.05 eg. Round 476.14 to 476.15, > 361.99 to 362.00, 375.36...

Short time vs short date
In my form I have a fldOutOfRoom which the user inputs a short time into the field, i.e, 0900. I have the following code in the open event of another form called frmRNnotes: If DateDiff("h", Forms!frmPtDemographicNew!frmVisitNewEdit.Form!OutOfRoom, Now) >= 1 Then Me.cmdRNnotesEdit.Visible = False This code gives the RN one hour to complete a note and then he/she can no longer edit the note. What I want to know is the Short Time format going to let the cmdRNnotesEdit button be visible everyday within one hour of the of the original time? That is, is the short time just a tim...

SetWindowsHookEx #3
Hi there. Could someone explane to me what is the purpose of dwThreadId, the last member of SetWindowsHookEx function? I've expected that this is a thread id with which hooks is associated. That means that hook is getting only messages produced by this particular thread. But it looks like I'm getting system wide messages anyway. so could somone make it clear for me? In fact I need to process a mesages of only one window. I know the thread id of that window. But! this thread is not in my process! And I don't want a real system wide hook because it slows down the box. Ok, I think ...

RMS 1.2 vs 1.3, plus integrate with Great Plains
1.) I am working on an assessment for upgrading our current RMS software from 1.2 to 1.3. My assessment will take in account the benefits, broken down by store operations (Retail) and the benefits to IT. Also, list risks and potential problems that might be experienced. We currently have 28 stores with 3+ registers per location with another 22 new stores on the schedule over the next 2 years. Are their other in this group of similiar size that have done this upgrade to the new version? 2.) If we do not upgrade the software, can we still integrate the RMS to Great Plains? Would we...

Date Calculation
Good Afternoon, I have a DB which tracks training of employees. The grace periods allowed with the training is that new training can be completed within 90 days of the expiry date without changing the anniversary date (e.g. the training is due on 1 April 2010, the employee conducts the training on 2 January 2010 but gets to keep the 1 April anniversary date). The table I am working with is mainly based on the date of training and the training type (which determines whether the training expires on the 1st of the 13th, 25th or 37th months or if it keeps the same date); what I w...

date modified
I have two sheets Data and Summary The "data" sheets macro extracts data from external file and paste into "Data" sheet Everytime the m acro is run to get latest data... The macro delete all contents of the "data" and then paste new data into the "data" sheet. Is there a way.. I can put a date on the "summary" sheet, when was the time the macro was run ( or in other words.. the data updated) This little macro records the date in the selected cell and formats it: Sub Macro2() Dim d As Date Dim s As String d = Now() s...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Need to add to current formula
I have this formula that will cause values to change based on the mont that is referenced in the formula (\$L\$1). Currently the formul is:=VLOOKUP(\$A\$1,\$AD\$7:\$AG\$44,IF(\$L\$1="January",2,IF(\$L\$1="February",2,IF(\$L\$1="March",2,IF(\$L\$1="April",2,IF(\$L\$1="MAY",4,IF(\$L\$1="June",3,IF(\$L\$1="July",3,0))))))),0) I need to add August, September, October, November, & December to thi formula but excel is not allowing me. Does anyone know how I can get around this? Oh by the way November thru April =2, May and October=4 and June thr...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Formula Problem?
I am using Excel 2000 with Windows XP. I am having a problem. I am on Sheet 2 of my workbook. I have SSN on a sheet named Employees in the same workbook. I need to take the numbers on the Employees Sheet and transfer it to the sheet 2. I know how to do this. It just won't work. This is a copy of my formula. =SUM(Employees!C3) This should take the SSN that is in the C3 cell on the employees sheet and place it at the cell where the formula is typed. When I put this formula in the cell I am getting just a "0". Please help. =Employees!C3 -- Kind regards, Niek Otten...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...