VBA Code Help - On Date

[BhuSan]

Hi All,

I need a VBA Code to perform the following action.

1. If I type a date in excel cell without slash followed by letter"D" then it should automatically change the cell to Date format.
2. For example in cell A1 if I type "11282014D" in the same cell it should change as "11/28/2014".

Please help me.

Thanks
Santhosh

 

[Hui]

Santhosh

Firstly, welcome to the Chandoo.org Forums

Why not just type 2014/11/28

 

[BhuSan]

Hi Hui,

In my Job I need to work on the dates a lot inorder to seed up my data entry fast I need this.

 

[Luke M]

While you could do this, the VB running would eliminate being able to use the Undo method, and we'd have to check for several conditions to make sure we don't accidentally change other items.

My first proposal would be to learn to type the dates correctly. The Number pad has a slash mark next to it, and learning to use it requires only 1 extra keystroke compared to your proposal.

Second proposal would be to use a formula like,
=IF(RIGHT(A2)="D",DATE(MID(A2,5,4),LEFT(A2,2),MID(A2,3,2)),A2)

 

[Khalid NGO]

Hi Hui,

In my Job I need to work on the dates a lot inorder to seed up my data entry fast I need this.

Hi Santosh,

If you are interested in speedup your data entries, you can use the idea of entering separate "Day","Month","Year". Then combine them using formula:

You just need to enter days serial wise (if required), copy/paste month & years.

See the attached example:

Additionally you can restrict your range to be enter only numbers (using data validation)

Regards,

 

[BhuSan]

Thanks for both Luke and Khalid