## Excel question

The war between wetware and hardware.
ed
Posts: 34429
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp

### Excel question

I have a column formatted as Date and they look like this 3/12/2018

If I use the Right function some of the values come out correct, others seem to be converted to text and then return crap, thus:

2011 9/14/2011
9472 1/25/2008
9472 1/25/2008
0594 2/20/2011
0247 3/10/2010
2001 1/1/2001
ScreenShot416.jpg
so it reads 3/10/2010 as 40247

WTF???

Thoughts?
You do not have the required permissions to view the files attached to this post.
Wenn ich Kultur höre, entsichere ich meinen Browning!

Abdul Alhazred
Posts: 73942
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago

### Re: Excel question

Internal date format. Force the data type to be "text" before applying the function.

Unless you are doing date arithmetic or sorting by dates, you can do this globally on your worksheet.
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

ed
Posts: 34429
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp

### Re: Excel question

Abdul Alhazred wrote:Internal date format. Force the data type to be "text" before applying the function.

Unless you are doing date arithmetic or sorting by dates, you can do this globally on your worksheet.
Apply "Text" get this

1/1/1988
9/14/2011
39472
39472
40594
40247
1/1/2001
42574
39657

Some dates, some crap.

Help. Thank you.
Wenn ich Kultur höre, entsichere ich meinen Browning!

Grammatron
Posts: 34156
Joined: Tue Jun 08, 2004 1:21 am
Location: Los Angeles, CA

### Re: Excel question

Clear all formats from those cels then apply a uniform date format

Rob Lister
Posts: 20361
Joined: Sun Jul 18, 2004 7:15 pm
Title: Incipient toppler
Location: Swimming in Lake Ed

### Re: Excel question

Pyrrho
Posts: 26863
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6

I bill at $225 per hour. The flash of light you saw in the sky was not a UFO. Swamp gas from a weather balloon was trapped in a thermal pocket and reflected the light from Venus. Grammatron Posts: 34156 Joined: Tue Jun 08, 2004 1:21 am Location: Los Angeles, CA ### Re: Excel question What about$3.50 and exposure?

Pyrrho
Posts: 26863
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6

### Re: Excel question

ed wrote:I have a column formatted as Date and they look like this 3/12/2018

If I use the Right function some of the values come out correct, others seem to be converted to text and then return crap, thus:

2011 9/14/2011
9472 1/25/2008
9472 1/25/2008
0594 2/20/2011
0247 3/10/2010
2001 1/1/2001

ScreenShot416.jpg

so it reads 3/10/2010 as 40247

WTF???

Thoughts?
You may need to trick out your RIGHT formula a bit, because the dates are themselves generated by an internal Excel formula.

Use the VALUE function to retrieve the characters you need.

Reference: https://support.office.com/en-us/articl ... 2d3953d8c2

For example:

Code: Select all

=VALUE(RIGHT(A1,4))
formula.JPG
You do not have the required permissions to view the files attached to this post.
Last edited by Pyrrho on Sat Jun 23, 2018 1:26 am, edited 1 time in total.
The flash of light you saw in the sky was not a UFO. Swamp gas from a weather balloon was trapped in a thermal pocket and reflected the light from Venus.

ed
Posts: 34429
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp

### Re: Excel question

jesus christ would you lot stop fucking around and answer the goddam question???
Wenn ich Kultur höre, entsichere ich meinen Browning!

Pyrrho
Posts: 26863
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6

### Re: Excel question

"WTF???"

No, I haven't lately.

"Thoughts?"

That's what they ask people at the office, as in "We need your thoughts." A bit vampiric if you ask me.
The flash of light you saw in the sky was not a UFO. Swamp gas from a weather balloon was trapped in a thermal pocket and reflected the light from Venus.

Abdul Alhazred
Posts: 73942
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago

### Re: Excel question

ed wrote:jesus christ would you lot stop fucking around and answer the goddam question???
Terribly sorry ed.

It's Gödel undecidable after all.
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

Witness
Posts: 19428
Joined: Thu Sep 19, 2013 5:50 pm

### Re: Excel question

ed wrote:I have a column formatted as Date
What's wrong with the YEAR function? (Be aware that I haven't touched a spreadsheet in years…)

Abdul Alhazred
Posts: 73942
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago

### Re: Excel question

Witness wrote:
ed wrote:I have a column formatted as Date
What's wrong with the YEAR function? (Be aware that I haven't touched a spreadsheet in years…)
Shhh!
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

Witness
Posts: 19428
Joined: Thu Sep 19, 2013 5:50 pm

### Re: Excel question

Abdul Alhazred wrote:Shhh!
Sorry!

Abdul Alhazred
Posts: 73942
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago

### Re: Excel question

Witness wrote:
Abdul Alhazred wrote:Shhh!
Sorry!
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

Pyrrho
Posts: 26863
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6

### Re: Excel question

Okay yeah so my solution basically sucked. Even with VALUE the formula returns Excel's encoding the date.

Witness's solution worked.
year.JPG
You do not have the required permissions to view the files attached to this post.
The flash of light you saw in the sky was not a UFO. Swamp gas from a weather balloon was trapped in a thermal pocket and reflected the light from Venus.

Pyrrho
Posts: 26863
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6

### Re: Excel question

Found this if it helps you. I know it helped me. I am filled with a new zest for life.

https://support.office.com/en-us/articl ... 471bbff252
Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.
The flash of light you saw in the sky was not a UFO. Swamp gas from a weather balloon was trapped in a thermal pocket and reflected the light from Venus.