My target range is "E4:E500" (Heading is date) . I want all the dates to be format dd-mmm (eg :- 5-feb , 14-mar etc ). If i enter only day then add current ongoing month to it (eg:- if 5 is entered then output should be 5-mar). If 5-4-2020 is entered then output should be 5-apr. Also i want to restrict the user . Only 1 to 31 numbers or a date shld be allowed else display warning.
Last edited by thebeastslayer; 03-06-2020 at 03:23 AM.
What you need to understand about dates and times in excel is…
a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Fri 06 Mar 2020) is actually 43896
Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
What you want is entirely do-able, excel tries to complete a date for you anyway, as long as you start entering it the right way. Entering 5/5 {enter} will produce 5-May by default, but you can also preset the format of those cells to only display day/month
To restrict entry to 1-31 IS technically possible - using data validation, but, because of how I explained how dates worked, excel will not actually "see" the days
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Thanks for info. Can u give me code that if 1-31 number is entered in the range then add 31 to it then change format to dd-mmm . It should do the trick . It will automatically convert jan to feb. I will alter the "add 31" according to the month i desire .
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks