Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Hi there,
Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Hi Dev,
If you pre-format the cell with the required date format, any date subsequently inserted will be displayed in the requisite format. --- Regards, Norman "Dev" wrote in message ... Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Take a look at Data Validation and see if that gets you what you want.
HTH, Barb Reinhardt "Dev" wrote: Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Just to add to Norm's excellent advice:
Dates are stored as the number of days since a base date. You format the cell to have it appear as you like. You can see this by entering the same date into a variety of adjacent cells. Then select those cells and format them as General and you will see they all hold the same value. Now format them as you want them to appear. -- Regards, Tom Ogilvy "Dev" wrote: Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Hi all,
Thank you so much for all your suggestions. The reason I am asking for defining a date format is primarily to compel the enterer to enter date in a specific format to avoid any errors. Normally I notice that different people updating a spreadsheet generally enter dates in different formats which causes a lot of issues while doing ageing analysis involving rework and reformatting to get it all in one standard. Hence if we can have a format predefined when the person is entering the date it will not allow him to make an error. I hope I have been clear in explaining. Thanks again Dev "Tom Ogilvy" wrote: Just to add to Norm's excellent advice: Dates are stored as the number of days since a base date. You format the cell to have it appear as you like. You can see this by entering the same date into a variety of adjacent cells. Then select those cells and format them as General and you will see they all hold the same value. Now format them as you want them to appear. -- Regards, Tom Ogilvy "Dev" wrote: Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
Hi Dev,
Perhaps, consider using a calendar control to enable your users to enter the date information. See Ron de Bruin at: Use the Calendar control to fill in dates http://www.rondebruin.nl/calendar.htm --- Regards, Norman "Dev" wrote in message ... Hi all, Thank you so much for all your suggestions. The reason I am asking for defining a date format is primarily to compel the enterer to enter date in a specific format to avoid any errors. Normally I notice that different people updating a spreadsheet generally enter dates in different formats which causes a lot of issues while doing ageing analysis involving rework and reformatting to get it all in one standard. Hence if we can have a format predefined when the person is entering the date it will not allow him to make an error. I hope I have been clear in explaining. Thanks again Dev "Tom Ogilvy" wrote: Just to add to Norm's excellent advice: Dates are stored as the number of days since a base date. You format the cell to have it appear as you like. You can see this by entering the same date into a variety of adjacent cells. Then select those cells and format them as General and you will see they all hold the same value. Now format them as you want them to appear. -- Regards, Tom Ogilvy "Dev" wrote: Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can we define a date format in a cell
there is no built in support for it.
As a test, I tried data validation to force the user to enter Jan . . . using a formula like =UPPER(LEFT(G5,3))="JAN" and it didn't work. I could just enter the letters Jan. This would indicate to me that data validation is looking at the underlying date serial number. the change event wouldn't kick in until after the entry is complete. So I don't see it as an option, but maybe someone else has a creative idea. -- Regards, Tom Ogilvy "Dev" wrote: Hi all, Thank you so much for all your suggestions. The reason I am asking for defining a date format is primarily to compel the enterer to enter date in a specific format to avoid any errors. Normally I notice that different people updating a spreadsheet generally enter dates in different formats which causes a lot of issues while doing ageing analysis involving rework and reformatting to get it all in one standard. Hence if we can have a format predefined when the person is entering the date it will not allow him to make an error. I hope I have been clear in explaining. Thanks again Dev "Tom Ogilvy" wrote: Just to add to Norm's excellent advice: Dates are stored as the number of days since a base date. You format the cell to have it appear as you like. You can see this by entering the same date into a variety of adjacent cells. Then select those cells and format them as General and you will see they all hold the same value. Now format them as you want them to appear. -- Regards, Tom Ogilvy "Dev" wrote: Hi there, Can we define a date format in a cell to restrict only dates with certain formats can be entered. Thanks for your help in advance. Dev |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Format Overrides to Date Format | Excel Worksheet Functions | |||
Generate sequential numbers with define format. | Excel Worksheet Functions | |||
How can I get date of file creation to XLS cell in date format? | Excel Worksheet Functions | |||
Define a cell value base on the date | Excel Discussion (Misc queries) | |||
How do I define a Format Cell Number Category | Excel Programming |