Date Formulas

[Guest]

How can I make a cell correct the year of a date

 

[JulieD]

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

 

[Harald Staff]

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

 

[Guest]

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?

 

[JulieD]

Hi Charlene

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

Cheers
JulieD

 

[CarlosAntenna]

Use data > validation to restrict the entry to dates greater than or equal
to 1/1/2005.

Carlos

 

[Guest]

Carlos, thank you, that is exactly what I want to do, but I don't know how to
do it. Can you help?

 

[Carlos Antenna]

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