×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hi, I need some help with Pre an

Hi, I need some help with Pre an

Hi, I need some help with Pre an

(OP)
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

RE: Hi, I need some help with Pre an

Try this:

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

RE: Hi, I need some help with Pre an

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

RE: Hi, I need some help with Pre an

(OP)
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

RE: Hi, I need some help with Pre an

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

---- Andy

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

RE: Hi, I need some help with Pre an

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

RE: Hi, I need some help with Pre an

Talking about a

Quote (Skip)

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

RE: Hi, I need some help with Pre an

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close