Customized Date Format

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!

"Frustrated" <[email protected]> wrote in message
news:[email protected]...
>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!

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" wrote:

>
>
>
> "Frustrated" <[email protected]> wrote in message
> news:[email protected]...
> >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!
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>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" wrote:
>
>>
>>
>>
>> "Frustrated" <[email protected]> wrote in message
>> news:[email protected]...
>> >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!
>>
>>
>>

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!!!!!

"Fred Smith" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" wrote:
> >
> >>
> >>
> >>
> >> "Frustrated" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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!
> >>
> >>
> >>
>
>
>

Glad to be of service.

--
Regards,
Fred


"Frustrated" <[email protected]> wrote in message
news:[email protected]...
> 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!!!!!
>
> "Fred Smith" wrote:
>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" wrote:
>> >
>> >>
>> >>
>> >>
>> >> "Frustrated" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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!
>> >>
>> >>
>> >>
>>
>>
>>