Hi, I need some help with Pre an

[cneill]

Hi,

I need some help with Pre and post year 1900 dates in Excel
The Date field is formatted to dd/mm/yyyy

For <1900 dates
I found this if Statement =IF(RIGHT(P16,4)>1900,RIGHT(P16,4),CONCATENATE(YEAR(P16)))
It returns the correct yyyy result in cells Q from the date in Column P
Strangely the If Statement uses >1900 and correctly returns all <1900 years, I am not sure why this is.

Now there are some date years that are >=1900
This if Statement works well, =IF(RIGHT(P17,4)<1900,RIGHT(P17,4),CONCATENATE(YEAR(P17)))
Strangely the If Statement uses <1900 and correctly returns all >1900 years, I am not sure why this is.
This does through up one strange result
If the cell is "Blank" it returns "1900" can this be corrected in the formula to return "Blank"

As I have various dates that contain years pre and post 1900, I would like to combine the two if statements so it giveS a result for all Pre and Post 1900 Years.

Can anyone help me with this If Statement?
Or is there a better way of doing this?
Thanks for everyone's help

 

[mintjulep]

Try this:

=VALUE(IF(ISTEXT(P1),RIGHT(P1,4),YEAR(P1)))

 

[combo]

1.
If all the dates are stored as text and the output is text, the only 'IF' you need should detect blank cells:
=IF(ISTEXT(P1),RIGHT(P1,4),"Blank")

2.
For numeric output:
=IF(ISTEXT(P1),VALUE(RIGHT(P1,4)),"Blank")
Note that in this case you will get a mixture of text and numbers in column, which is not a best for future column processing.

3.
For mixed input (dates for proper dates, text if year<1900), you can modify mintjulep's formula to handle blanks and embed IFs:
=IF(ISBLANK(P1),"blank",IF(ISTEXT(P1),RIGHT(P1,4),YEAR(P1))
and decide if what type of output (text or number) you need for the year.


combo

 

[cneill]

Hi combo,
This works great
=IF(ISBLANK(P1),"blank",IF(ISTEXT(P1),RIGHT(P1,4),YEAR(P1)) using number for the year.
Thank you so much

 

[Andrzejek]

Here is some more info about pre-1900 dates in Excel

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Hi,

I've dabbled a bit in long date span calculations because of my interest in the very few Biblical prophecise that are associated with dateable events and times. Without going into those details, I come away from that exersize with these suggestions.

I used 365.2425 as the number of days per year, accounting for leap years and leap centuries.
I constructed a table of 366 rows for calculating fractions of a year for the beginning and ending dates, accounting for specific years leap or not.

For instance my birthday is Feb 7, 1942.
There are 327 of 365 days left in 1942
Today is day 99 of 366
The duration is 30011 days
Using 365.2425 days/year and the fractions of a year at both ends, I calculate 30010.7

Now that's not a formula but a method.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

Talking about a

... few Biblical prophecise that are associated with dateable events and times.

Calendar skipped 11 days in 1752, there are 10 days missing from October 1582, Mayan forecasted impending doom in 2012, the list goes on and on.
People would have to know about the dates' adjustments way before they happened.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Andy, the key word is "people." A true prophecy is a revelation from the Creator. He gave Moses the key in Deuteronomy 18:15-22. A true prophet must bat 1000. A pretender is only stating what people can only know, guess or fabricate.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!