Date format

[Gerry Cornell]

I have created a Data Input Form in Excel for use with an Accounting package. The good news is it works quite well, albeit it may need a few tweaks. I am, however, encountering a problem, which although I have a workaround, needs a better solution and this is where I would appreciate some help.

The Accounting package require a date field to be in a very specific format. The Date must be 10 digits comprising NN/NN/NNNN where N is a numeral. Any day or month under 10 must have a leading zero. The sequence is DAY/MONTH/YEAR. It important that the separator used is a forward slash . A non-conforming date cannot be imported into the Accounting.

Do I need a Custom Date Format? If yes what is it? If not what other solutions are you able to suggest.

The workaround involves changing the date to Text, inserting leading Zeros and changing dots to forward slashes etc. It is too time consuming. After stripping extraneous material from the Worksheet I am saving the Excel file to CSV format and importing the CSV file into the Accounting package.



~~~~~~

TIA.

Gerry

~~~~~~~~~~~~~~~~~~~~~~~~
FCA

Stourport, Worcs, England
Enquire, plan and execute.
~~~~~~~~~~~~~~~~~~~~~~~~

 

[Michael Hopwood]

Are you using the Form to poulate a worksheet?

If so, use the following formula to convert a date to text in your specified
format:

=TEXT(NOW(),"dd/mm/yyyy")

--
Michael Hopwood


I have created a Data Input Form in Excel for use with an Accounting
package. The good news is it works quite well, albeit it may need a few
tweaks. I am, however, encountering a problem, which although I have a
workaround, needs a better solution and this is where I would appreciate
some help.

The Accounting package require a date field to be in a very specific format.
The Date must be 10 digits comprising NN/NN/NNNN where N is a numeral. Any
day or month under 10 must have a leading zero. The sequence is
DAY/MONTH/YEAR. It important that the separator used is a forward slash . A
non-conforming date cannot be imported into the Accounting.

Do I need a Custom Date Format? If yes what is it? If not what other
solutions are you able to suggest.

The workaround involves changing the date to Text, inserting leading Zeros
and changing dots to forward slashes etc. It is too time consuming. After
stripping extraneous material from the Worksheet I am saving the Excel file
to CSV format and importing the CSV file into the Accounting package.



~~~~~~

TIA.

Gerry

~~~~~~~~~~~~~~~~~~~~~~~~
FCA

Stourport, Worcs, England
Enquire, plan and execute.
~~~~~~~~~~~~~~~~~~~~~~~~