|
|
 |
 |
Joined: 11/9/2009
Posts: 47
|
|
|
Hi,
In an Excel user will able to input date in A:A column only which should be lass than today with follwoing recognisable format for further excel date calculation
DD.MM.YYYY
Regards
Manik
Best Regards
Manik Nag
|
|
|
|
 | |
|
|
|
Hi,
A dot is not a recognised date seperator. Therefore date entered in DD.MM.YYYY will not allow you to perform calculations. If you want to perform calculations, you will have to use a valid date seperator such as / or -
You may go to Data > Validation > Allow > Date > Less than. In the End date box, enter =TODAY()
Hope this helps.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Hi,
Please let me know how can I apply the following formula under Data>Validation
=IF(A1>TODAY(),TEXT(TODAY(),"dd.mm.yyyy"),TEXT(A1,"dd.mm.yyyy"))
If this can be done through data validation then my purpose get resolved (i.e. I can input any date in a cell with DD.MM.YYYY format)
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
Try this
Data > Validation > Settings > Allow > Between. In the Start Date, enter 1/1/1900 and in the End date, enter =today()
You may then format the date as dd.mm.yyyy i.e. Ctrl+1 > Number > Custom > Type.
Hope this helps.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Sir,
Thanks , this is happening I can input any date in a cell with DD.MM.YYYY format but after that I need it to upload this database to another application DD.MM.YYYY format only where as the format showing is DD.MM.YYYY in a cell but it's actually it's DD/MM/YYYY format
can it be done throgh in VBA
Regards
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
Try to find for / and replace with .
If this solution does not suit you, a macro can be created. First try the simpler find and replace solution.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
hi,
Thanks for your reply, it.s happening but my whole objective is an autometed process as I earlier mentioned I will only input the date and it will input as well as show dd.mm.yyyy format only (where replace data or formatting cells etc - manual activity does not required)
Regards
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
So you want the Find and Replace step to be automated. What is the range of cells in which you will enter dates?
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Hi , Ashish
I simply want to share a excel among 10 different users who will be resticted to entre date in dd.mm.yyyy format only in A:A column (user will not able to input anydata/change any format replace any valus etc except enter date valus in dd.mm.yyyy format only) after that I have to upload this data in SAP (where the date format are dd.mm.yyyy at the time of upload)
Regards
Manik
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
Ok, then try this validation rule under Data > Validation > Allow > Custom in cell A1
=AND(1*SUBSTITUTE(A1,".","/")>DATE(1900,1,1),1*SUBSTITUTE(A1,".","/")<=TODAY(),LEN(A1)=10,SUMPRODUCT(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=".")))
Hope this helps. Do let me know how this works.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Dear Sir,
You are Mastermind Iyes, t's working, thank you so much, but I've checked user are not restricted by dot(.); slash(/) and Dash(-) are not restricted, can it be done? otherwise it's OK
Regards
Manik
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
You are welcome. Thank you for your kind words. Glad my solution worked. If you wish to allow the user to enter any one of /, - or ., then try this Custom validation
=OR(AND(A1>=DATE(1900,1,1),A1DATE(1900,1,1),1*SUBSTITUTE(A1,".","/")<=TODAY(),LEN(A1)=10,SUMPRODUCT(1*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="."))))
Hope this helps. Do let me know how this works.
A word of caution here, the above validation will allow a person to enter a simple number e.g. 12. This is because a date is nothing but a number. Number 12 is 12 January 1900.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Sir,
Thanks for the detailed discussion, Thanks again for your help, I wish not to allow users any one of /, - thats all.
Regards
Manik
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Manik, i am very confused about waht you want. Please state clearly. My original solution allowed the user to enter . only. Now what are you looking for - should the user be allowed to enter / and - also.
Please explain elaborately.
|
|
|
|
| |
|
|
|
|
Joined: 11/9/2009
Posts: 47
|
|
|
Sir,
user should not be allowed to enter / and -
Regards
Manik
Best Regards
Manik Nag
|
|
|
|
| |
|
|
|
Hi,
Well that is what my original formula does. The formula suggested earlier allows the user to enter dot seperator only.
Please check thoroughly and post back.
|
|
|
|