Entering January dates

L

leszczuk

New Member
Joined
Feb 25, 2003
Messages
10
I have a schedule in a worksheet containing alternating columns of successive events and their dates: each date is later than the dates to its left in the same row. At this time of the year there is a problem because unless you enter the year, Excel assumes that months in the new year are instead in 2014. So: Event 1, in date cell type “11/11” – displays “11.11.14”. Event 2, in date cell type “12/12” – displays “12.12.14”. Event 3, in date cell type “1/1” – displays “1.1.14”.

I realise (as well as typing the three extra characters /15) I could have a second column each time subtracting 365 but that’s too elaborate. Is there a quiet, simple solution?
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
Can you use a formula so that you only need to enter the first date and the rest are automatically generated?
 
Upvote 0
... or if the dates can't be generated you could put validation on the date so that it has to be greater than the previosu date cell
 
Upvote 0
Assuming you are using Excel 2010 ...... go to the Data ribbon and click on Data Validation ...then in the Settings Tab select Date from the dropdown list; then "greater than" ... and finally in Start Date select the cell that is to the left of the cell you are checking against
 
Upvote 0
Thanks for your help. But entering "1/1" results in the error message "The value you entered is not valid. A user has restricted values that can be entered in this cell."
 
Upvote 0
There isn't a pattern like that; there is no regular interval between events. I'm recording deadlines of successive events, so the only time-based rule is that no dated entry can be earlier than an entry to the left on the same line.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Inputting date always in the past
Replies
5
Views
123
Joe4
Joe4
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
439
Jaxi
J
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
336
Agnarr
Agnarr
O
  • Solved
Counting instances of unique entries, with conditions
Replies
1
Views
384
ollyhughes1982
O
S
  • Question
Inconsistent behaviour interpreting dates, variously as dd/mm or mm/dd
Replies
3
Views
319
MARK858
MARK858

Forum statistics

Threads
1,214,585
Messages
6,120,391
Members
448,957
Latest member
Hat4Life

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top