Excel question

The war between wetware and hardware.
User avatar
ed
Posts: 32375
Joined: Tue Jun 08, 2004 11:52 pm
Title: Trilobite of the Florida swamp
Has thanked: 414 times
Been thanked: 693 times

Excel question

Postby 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.
- new minimalist ethos -

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

Re: Excel question

Postby 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: 32375
Joined: Tue Jun 08, 2004 11:52 pm
Title: Trilobite of the Florida swamp
Has thanked: 414 times
Been thanked: 693 times

Re: Excel question

Postby 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.
- new minimalist ethos -

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

Re: Excel question

Postby Grammatron » Fri Jun 22, 2018 5:41 pm

Clear all formats from those cels then apply a uniform date format
pillory wrote:jokes aren't funny....seriously thinking......

seriously thinking might be funny....but it's not joke

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

Re: Excel question

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

Define your datatype as XsubY and then placate your insizers

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

Re: Excel question

Postby 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: 32785
Joined: Tue Jun 08, 2004 1:21 am
Location: Los Angeles, CA
Been thanked: 1550 times

Re: Excel question

Postby Grammatron » Fri Jun 22, 2018 11:11 pm

What about $3.50 and exposure?
pillory wrote:jokes aren't funny....seriously thinking......

seriously thinking might be funny....but it's not joke

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

Re: Excel question

Postby 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: 32375
Joined: Tue Jun 08, 2004 11:52 pm
Title: Trilobite of the Florida swamp
Has thanked: 414 times
Been thanked: 693 times

Re: Excel question

Postby ed » Sat Jun 23, 2018 1:24 am

jesus christ would you lot stop fucking around and answer the goddam question???
- new minimalist ethos -

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

Re: Excel question

Postby 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: 69301
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 2739 times
Been thanked: 1056 times

Re: Excel question

Postby 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: 14782
Joined: Thu Sep 19, 2013 5:50 pm
Has thanked: 1732 times
Been thanked: 2316 times

Re: Excel question

Postby 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: 69301
Joined: Mon Jun 07, 2004 1:33 pm
Title: Yes, that one.
Location: Chicago
Has thanked: 2739 times
Been thanked: 1056 times

Re: Excel question

Postby 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: 14782
Joined: Thu Sep 19, 2013 5:50 pm
Has thanked: 1732 times
Been thanked: 2316 times

Re: Excel question

Postby Witness » Sat Jun 23, 2018 1:50 am

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

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

Re: Excel question

Postby 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: 25277
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2624 times
Been thanked: 2627 times

Re: Excel question

Postby 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: 25277
Joined: Sat Jun 05, 2004 2:17 am
Title: Man in Black
Location: Division 6
Has thanked: 2624 times
Been thanked: 2627 times

Re: Excel question

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


Return to “Computers”

Who is online

Users browsing this forum: CCBot [Bot] and 0 guests