Customized Date Format

[Frustrated]

I have set up a customized date format in Excel that works with a txt file.
However, if the date is only the year, i.e. 1955, without a day or month, it
is interpreting the year as an unrelated date, i.e. 19 May 1905. The format
is customized to read the abbreviated month as the full word for the month,
but I can see I need to customize the format to accommodate just the year
entries as well.

Can this be done by a special format that will accommodate both types of
date entries?

Any help would be appreciated, and thanks!

 

[Fred Smith]

I have set up a customized date format in Excel that works with a txt file.

If would be useful to see this customized format

However, if the date is only the year, i.e. 1955, without a day or month, it
is interpreting the year as an unrelated date, i.e. 19 May 1905.

Excel stores dates as the number of days since Jan 1, 1900. When it sees 1955,
it thinks this is the number of days since then, hence the result of 19 May
1905. With a formula, you could change it to some date in that year, such as:
=if(a1<2100,date(year(a1),1,1)),a1)

The format
is customized to read the abbreviated month as the full word for the month,
but I can see I need to customize the format to accommodate just the year
entries as well.

Formats can't "read" anything in the cell. Only formulas can. Are you saying you
have a formula which translates the abbreviated month to the full month? If so,
post the formula, and the additional situation you want handled (ie, numbers
less than some year), and you'll almost certainly get an answer to your problem

Can this be done by a special format that will accommodate both types of
date entries?

Very likely, but we need an example of both types.

 

[Frustrated]

I don't need someone arguing "semantics" with me (!)

My customized Excel date format in the date column of cells reads: d mmmm
yyyy. This only works when the date in the text file that it is "reading"
the data from has a date that reads: 10 Jan 1955. If the text file only has
the year because the exact date is unknown, the custom format doesn't work.
I need a formula/format in the date column cells that will accommodate both
types of data entry. The excel file is merged with a variety of label files
in Word.

If you don't understand this, then perhaps someone else does!

Thanks....

 

[Fred Smith]

I'm sorry that you interpreted my request for more information as "semantics"
and took offense to it. Sometimes when you're asking for free help, you have to
put up with crotchety old people like me. Fortunately the additional information
you provided in your response should be enough to solve the problem.

Excel is converting the data from your text file as best it can. When it sees
the field "10 Jan 1955", it recognizes it as a date, and converts it as such. As
I said previously, dates to Excel are the number of days since Jan 1, 1900. So
Excel will convert your field to the number 20,099 and store it in the cell.
When it sees only "1955" in a field, it recognizes this as a number, and simply
stores that number in the cell.

Now when you apply your format to the field, Excel has no way of knowing where
the number originally came from. It simply looks at the number in the cell,
determines how many days have elapsed since Jan 1, 1900 and displays the
appropriate date. So 20,099 will be displayed as "10 January 1955", which is
what you want, but 1955 will be displayed as "8 May 1905" because May 8th, 1905
is 1955 days from the start of the century. If your original data was the year
1966, Excel would display this as "19 May 1905".

You can solve your problem using a custom format like:

[<2100]####;d mmmm yyyy

This tells Excel: If the number in the cell is less than 2100 (which would
happen if your source data was only the year), display it as a four digit
number; if it's anything else, display it in long date format.

Hope this helps
Fred

 

[Frustrated]

Thank you Fred! I knew someone would have a solution, so I'm glad you
weren't put off by my terse retort. This forum has been very helpful to me
in the past, and I appreciate the help that is dispensed freely.

By the way...it works like a charm!!!!!

I'm sorry that you interpreted my request for more information as "semantics"
and took offense to it. Sometimes when you're asking for free help, you have to
put up with crotchety old people like me. Fortunately the additional information
you provided in your response should be enough to solve the problem.

Excel is converting the data from your text file as best it can. When it sees
the field "10 Jan 1955", it recognizes it as a date, and converts it as such. As
I said previously, dates to Excel are the number of days since Jan 1, 1900. So
Excel will convert your field to the number 20,099 and store it in the cell.
When it sees only "1955" in a field, it recognizes this as a number, and simply
stores that number in the cell.

Now when you apply your format to the field, Excel has no way of knowing where
the number originally came from. It simply looks at the number in the cell,
determines how many days have elapsed since Jan 1, 1900 and displays the
appropriate date. So 20,099 will be displayed as "10 January 1955", which is
what you want, but 1955 will be displayed as "8 May 1905" because May 8th, 1905
is 1955 days from the start of the century. If your original data was the year
1966, Excel would display this as "19 May 1905".

You can solve your problem using a custom format like:

[<2100]####;d mmmm yyyy

This tells Excel: If the number in the cell is less than 2100 (which would
happen if your source data was only the year), display it as a four digit
number; if it's anything else, display it in long date format.

Hope this helps
Fred

I don't need someone arguing "semantics" with me (!)

My customized Excel date format in the date column of cells reads: d mmmm
yyyy. This only works when the date in the text file that it is "reading"
the data from has a date that reads: 10 Jan 1955. If the text file only has
the year because the exact date is unknown, the custom format doesn't work.
I need a formula/format in the date column cells that will accommodate both
types of data entry. The excel file is merged with a variety of label files
in Word.

If you don't understand this, then perhaps someone else does!

Thanks....

 

[Fred Smith]

Glad to be of service.

--
Regards,
Fred

Thank you Fred! I knew someone would have a solution, so I'm glad you
weren't put off by my terse retort. This forum has been very helpful to me
in the past, and I appreciate the help that is dispensed freely.

By the way...it works like a charm!!!!!

I'm sorry that you interpreted my request for more information as "semantics"
and took offense to it. Sometimes when you're asking for free help, you have
to
put up with crotchety old people like me. Fortunately the additional
information
you provided in your response should be enough to solve the problem.

Excel is converting the data from your text file as best it can. When it sees
the field "10 Jan 1955", it recognizes it as a date, and converts it as such.
As
I said previously, dates to Excel are the number of days since Jan 1, 1900.
So
Excel will convert your field to the number 20,099 and store it in the cell.
When it sees only "1955" in a field, it recognizes this as a number, and
simply
stores that number in the cell.

Now when you apply your format to the field, Excel has no way of knowing
where
the number originally came from. It simply looks at the number in the cell,
determines how many days have elapsed since Jan 1, 1900 and displays the
appropriate date. So 20,099 will be displayed as "10 January 1955", which is
what you want, but 1955 will be displayed as "8 May 1905" because May 8th,
1905
is 1955 days from the start of the century. If your original data was the
year
1966, Excel would display this as "19 May 1905".

You can solve your problem using a custom format like:

[<2100]####;d mmmm yyyy

This tells Excel: If the number in the cell is less than 2100 (which would
happen if your source data was only the year), display it as a four digit
number; if it's anything else, display it in long date format.

Hope this helps
Fred

I don't need someone arguing "semantics" with me (!)

My customized Excel date format in the date column of cells reads: d mmmm
yyyy. This only works when the date in the text file that it is "reading"
the data from has a date that reads: 10 Jan 1955. If the text file only
has
the year because the exact date is unknown, the custom format doesn't work.
I need a formula/format in the date column cells that will accommodate both
types of data entry. The excel file is merged with a variety of label
files
in Word.

If you don't understand this, then perhaps someone else does!

Thanks....

:




I have set up a customized date format in Excel that works with a txt
file.
If would be useful to see this customized format

However, if the date is only the year, i.e. 1955, without a day or
month,
it
is interpreting the year as an unrelated date, i.e. 19 May 1905.
Excel stores dates as the number of days since Jan 1, 1900. When it sees
1955,
it thinks this is the number of days since then, hence the result of 19
May
1905. With a formula, you could change it to some date in that year, such
as:
=if(a1<2100,date(year(a1),1,1)),a1)

The format
is customized to read the abbreviated month as the full word for the
month,
but I can see I need to customize the format to accommodate just the
year
entries as well.
Formats can't "read" anything in the cell. Only formulas can. Are you
saying
you
have a formula which translates the abbreviated month to the full month?
If
so,
post the formula, and the additional situation you want handled (ie,
numbers
less than some year), and you'll almost certainly get an answer to your
problem


Can this be done by a special format that will accommodate both types of
date entries?
Very likely, but we need an example of both types.


Any help would be appreciated, and thanks!