Excel help

[highfiveoh]

This is an easy one I'm sure, but I'm lost. I have a cell (E5) that has a birthdate (mm/dd/yyyy) and I'd like have a cell that just has the 4-digit year. Thanks for any guidance! :)

[highfiveoh]

Bleh, I should mention that I need this cell to be next to it.

[GoldenEagle]

I think you would use the FormatDateTIme function and then pass in a parameter for the specifics.

[highfiveoh]

I'm not quite certain what you're suggesting. I did find something on google using 'opposite of concatenate' since that is what I think I'm trying to do. But when I do =right(e5,4) I get a totally different number than the year I'm looking for. I want the year separated from the month and day so I can subtract it from a different year. And I realize I can just subtract two dates, but I don't want any months or days involved in the formula. Just one 4-digit year minus the other.

[Northwood_DK]

If you call cell you want with the year =(E5) and then go to “format cell” and “Own definitions” and type inn YYYY it should give you the year only.

There is possible a “more correct” way to do this, but this worked for me.

[FrogMan]

If you call cell you want with the year =(E5) and then go to “format cell” and “Own definitions” and type inn YYYY it should give you the year only.

There is possible a “more correct” way to do this, but this worked for me.

while this will "show" the year, the number behind it will still be the serial number of the day, i.e. 39065 for December 14, 2006. If you want to extract the year only from a date, you could use the YEAR(E5) function.

So with the birthdate in E5, type this in F5:
=YEAR(E5)

Should do the trick.

Good luck with that.

FM

[bryce]

Could you not also just have F5 = E5 and then just format F5 to just show the year, I think that would work, too...

[FrogMan]

Could you not also just have F5 = E5 and then just format F5 to just show the year, I think that would work, too...

that is what Northwod_DK said but as I pointed out, it's a format change only, the real number behind the format is still the whole date serial number. If you need to use the year to work on, using the YEAR() function would work better...

FM

[bryce]

that is what Northwod_DK said but as I pointed out, it's a format change only, the real number behind the format is still the whole date serial number. If you need to use the year to work on, using the YEAR() function would work better...

FM

Ah, yea, I see what you're getting at, good call. You're right, if he wants to work with years and use formulas using years, gotta go with your method.

[Fighter of Foo]

Or copy, paste special, values only; and then use the right function above.

[FrogMan]

Or copy, paste special, values only; and then use the right function above.


I'll be stubborn again but doing that will return you 39065 for the date of December 14, 2006. It's the serial number of the date behind the format. The right function would be useless then...

FM

[highfiveoh]

Thanks for all the replies, fellas. Sounds like FrogMan got the gist of what I wanted to do. I did, however, find a workaround for what I actually wanted to do - of course it sort of went against what I was asking for. I just used a date of 12/31/06 - birth date. That seemed to do what I wanted as well.

[Fighter of Foo]

Need some help on this one:

I've copied/pasted a bunch of companies with address info off of a website into excel. Data is in the following format from each company:

NAME
ADDRESS1
ADDRESS2
PHONE
URL

I need to move this data from columns into rows, with the headers above on top of each row. If you can picture all of this data neatly arranged starting in A1, the first company name would be located in A1, the second in A6 and so on.

Any ideas? I'm probably more retarded than usual this morning.

Thanks as always.

[gottimd]

Copy---> Paste Special--->Transpose

[Fighter of Foo]

That works if I do each company individually. I have around 500. :(

[gottimd]

That works if I do each company individually. I have around 500. :(

Works fine for me. I did a small example of 3 Columns and copied and pasted, and I did vice versa. You can do more than one column/row at a time.

[Fighter of Foo]

Right, but I'm still in the same spot. Trying to turn this:

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15

Into this:

A1 A2 A3 A4 A5
A6 A7 A8 A9 A10
A11 A12 A13 A14 A15

[MIJB#19]

Right, but I'm still in the same spot. Trying to turn this:

A1
A2
A3
A4
A5
A6
A7
A8
A9
A10
A11
A12
A13
A14
A15

Into this:

A1 A2 A3 A4 A5
A6 A7 A8 A9 A10
A11 A12 A13 A14 A15

Work around: Add an additional column to the left (or right if that works better) going:
1
2
3
4
5
1
2
3
4
5
etc.

Then, order by the 1-5. Now, you have to manually cut-and-paste the stuff in the correct columns oder:
1's 2's 3's 4's 5's

Don't be shy of the undo function if things go wrong.

[Fighter of Foo]

Thanks. This makes sense. Easy enough to sort ascending.