Greetings,

I am creating a log for photos with varying levels of detail:

- some have firm dates (dd/mm/yyyy)

- some have mm/yyyy

- some have no date, so I am forced to approximate, e.g. early 1930s, mid 1940s, etc.

Is it at all possible to include this last category in a date column without having to cut and paste these entries by hand?

Thank you

On Tue, 22 Oct 2013 21:30:47 +0100, ernstblofeld wrote:


Greetings,

I am creating a log for photos with varying levels of detail:

- some have firm dates (dd/mm/yyyy)

- some have mm/yyyy

- some have no date, so I am forced to approximate, e.g. early 1930s,
mid 1940s, etc.

Is it at all possible to include this last category in a date column
without having to cut and paste these entries by hand?

Thank you

Yes it is.

You would use a formula that would either pick up the year, if it is a true date, or the contiguous four digit groups, if it is not (and if there were no other contiguous four digit groups in your strings; if there were, more logic would be needed).

If your version of Excel is 2007 or later, and your date is in A2, for example, you could use:

=IFERROR(YEAR(A2),LOOKUP(9.9E+307,--MID(A2,ROW(INDIRECT("1:"&LEN(A2)-3)),4)))

And fill down as needed.

If that doesn't work, you will need to supply the details as to the nature of your data.

On Wed, 23 Oct 2013 12:43:38 -0400, Ron Rosenfeld wrote:

On Tue, 22 Oct 2013 21:30:47 +0100, ernstblofeld wrote:


Greetings,

I am creating a log for photos with varying levels of detail:

- some have firm dates (dd/mm/yyyy)

- some have mm/yyyy

- some have no date, so I am forced to approximate, e.g. early 1930s,
mid 1940s, etc.

Is it at all possible to include this last category in a date column
without having to cut and paste these entries by hand?

Thank you

Yes it is.

You would use a formula that would either pick up the year, if it is a true date, or the contiguous four digit groups, if it is not (and if there were no other contiguous four digit groups in your strings; if there were, more logic would be needed).

If your version of Excel is 2007 or later, and your date is in A2, for example, you could use:

=IFERROR(YEAR(A2),LOOKUP(9.9E+307,--MID(A2,ROW(INDIRECT("1:"&LEN(A2)-3)),4)))

And fill down as needed.

If that doesn't work, you will need to supply the details as to the nature of your data.

My answer was incomplete.

The result of that formula is the year, which you could sort by

If you want to sort by the actual date, and all of the dates are not ealier than 1/1/1900, then use this formula instead:

=IFERROR(--A2,DATE(LOOKUP(9.9E+307,--MID(A2,ROW(INDIRECT("1:"&LEN(A2)-3)),4)),7,1))

=IFERROR(--A2,IF(LOOKUP(9.9E+307,--MID(A2,ROW(INDIRECT("1:"&LEN(A2)-3)),4))<1900,"
<1900",DATE(LOOKUP(9.9E+307,--MID(A2,ROW(INDIRECT("1:"&LEN(A2)-3)),4)),7,1)))

If will check that the dates are in the range Excel can use, and convert those "approximates" to July 1 of the year.