Excel question

The war between wetware and hardware.
User avatar
ed
Posts: 33310
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp
Has thanked: 451 times
Been thanked: 777 times

Excel question

Post by ed » Fri Jun 22, 2018 12:34 pm

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!

User avatar
Abdul Alhazred
Posts: 71428
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 3322 times
Been thanked: 1228 times

Re: Excel question

Post by Abdul Alhazred » Fri Jun 22, 2018 12:39 pm

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.
Image "If I turn in a sicko, will I get a reward?"

"Yes! A BIG REWARD!" ====> Click here to turn in a sicko
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

User avatar
ed
Posts: 33310
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp
Has thanked: 451 times
Been thanked: 777 times

Re: Excel question

Post by ed » Fri Jun 22, 2018 1:41 pm

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!

User avatar
Grammatron
Posts: 33586
Joined: Tue Jun 08, 2004 1:21 am
Location: Los Angeles, CA
Been thanked: 1747 times

Re: Excel question

Post by Grammatron » Fri Jun 22, 2018 5:41 pm

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

User avatar
Rob Lister
Posts: 19922
Joined: Sun Jul 18, 2004 7:15 pm
Title: Incipient toppler
Location: Swimming in Lake Ed
Has thanked: 593 times
Been thanked: 596 times

Re: Excel question

Post by Rob Lister » Fri Jun 22, 2018 10:37 pm

Define your datatype as XsubY and then placate your insizers

User avatar
Pyrrho
Posts: 25957
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2723 times
Been thanked: 2785 times

Re: Excel question

Post by Pyrrho » Fri Jun 22, 2018 10:49 pm

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.

User avatar
Grammatron
Posts: 33586
Joined: Tue Jun 08, 2004 1:21 am
Location: Los Angeles, CA
Been thanked: 1747 times

Re: Excel question

Post by Grammatron » Fri Jun 22, 2018 11:11 pm

What about $3.50 and exposure?

User avatar
Pyrrho
Posts: 25957
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2723 times
Been thanked: 2785 times

Re: Excel question

Post by Pyrrho » Sat Jun 23, 2018 1:24 am

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.

User avatar
ed
Posts: 33310
Joined: Tue Jun 08, 2004 11:52 pm
Title: Rhino of the Florida swamp
Has thanked: 451 times
Been thanked: 777 times

Re: Excel question

Post by ed » Sat Jun 23, 2018 1:24 am

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

User avatar
Pyrrho
Posts: 25957
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2723 times
Been thanked: 2785 times

Re: Excel question

Post by Pyrrho » Sat Jun 23, 2018 1:27 am

In reply to your 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.

User avatar
Abdul Alhazred
Posts: 71428
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 3322 times
Been thanked: 1228 times

Re: Excel question

Post by Abdul Alhazred » Sat Jun 23, 2018 1:41 am

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. :P
Image "If I turn in a sicko, will I get a reward?"

"Yes! A BIG REWARD!" ====> Click here to turn in a sicko
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

User avatar
Witness
Posts: 16861
Joined: Thu Sep 19, 2013 5:50 pm
Has thanked: 2058 times
Been thanked: 2821 times

Re: Excel question

Post by Witness » Sat Jun 23, 2018 1:41 am

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…) :?

User avatar
Abdul Alhazred
Posts: 71428
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 3322 times
Been thanked: 1228 times

Re: Excel question

Post by Abdul Alhazred » Sat Jun 23, 2018 1:43 am

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! :notsure:
Image "If I turn in a sicko, will I get a reward?"

"Yes! A BIG REWARD!" ====> Click here to turn in a sicko
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

User avatar
Witness
Posts: 16861
Joined: Thu Sep 19, 2013 5:50 pm
Has thanked: 2058 times
Been thanked: 2821 times

Re: Excel question

Post by Witness » Sat Jun 23, 2018 1:50 am

Abdul Alhazred wrote:Shhh! :notsure:
:oops: Sorry!

User avatar
Abdul Alhazred
Posts: 71428
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 3322 times
Been thanked: 1228 times

Re: Excel question

Post by Abdul Alhazred » Sat Jun 23, 2018 2:04 am

Witness wrote:
Abdul Alhazred wrote:Shhh! :notsure:
:oops: Sorry!
:busted:
Image "If I turn in a sicko, will I get a reward?"

"Yes! A BIG REWARD!" ====> Click here to turn in a sicko
Any man writes a mission statement spends a night in the box.
-- our mission statement plappendale

User avatar
Pyrrho
Posts: 25957
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2723 times
Been thanked: 2785 times

Re: Excel question

Post by Pyrrho » Sat Jun 23, 2018 4:17 pm

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.

User avatar
Pyrrho
Posts: 25957
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2723 times
Been thanked: 2785 times

Re: Excel question

Post by Pyrrho » Sat Jun 23, 2018 4:22 pm

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.