Converting Time Formats Without Colons

I had to do what you see below.  Not easy to do when you have 16,000 times you have to convert.  So, I put a post on the Mr. Excel message board and one of the other outstanding members gave me the formula you see below.  It's amazing!

Problem:

801a   to 8:00 AM

1000p to 10:00 PM

Formula:

=IF(OR(RIGHT(A1)={"a","p"}),(TEXT(LEFT(A1,LEN(A1)-1),"00\:00 ")&RIGHT(A1)&"M")+0,"")

http://www.mrexcel.com/forum/showthread.php?t=384863

DOW Drops Below 7,000

I am not going to say much, but.....when the Dow drops below 5,000--which I thing it may--then I will have something to say.

Retrieving the Column Letter

The other day I was trying to do some kinda formula that involved needing the column letter. I know, if one types =COLUMN() you will get the column number. BUT WHAT ABOUT THE LETTER? This formula only goes up to column “ZZ,” AKA column "702," but that’s all I needed.

=MID(ADDRESS(ROW(),COLUMN(),4),1,IF(COLUMN()<27,1,2))

=RAND() in Word? What the #$%&?

Have you ever used the =RAND() function in Excel? If you haven’t, it basically just generates a random number from zero to one, e.g. “0.34874376.” No this isn’t a surprise, but what is a surprise is that it works in Word. Go ahead and type “=RAND()” into Word and see what happens. Hint: you don’t get numbers.

How to Remove Excess Periods

The other day I came across the most annoying formatting that was a major pain-in-the-ass undo.
. Envelopes………………………..
. Paper……………………………….
. Staples…………………………….
Imagine having to go into each cell, highlighting a bunch of little dots and then deleting them. Then repeat this a couple hundred times. YIKES!!! So, on my journey to work smarter and not the other way, I discovered something interesting. Excel thinks that 1 period is a single charter and that 3 periods is also a single character. So, what do you do when you’ve got butt-load periods? Which number of periods do you try to eliminate? The answer is you have to deal with both. Here are the character numbers:

. = CHAR(46)
… =CHAR(133)

I won’t bore you with the details, but here is the magic formula that did the job:

=LEFT(A1,(IF(ISERROR(FIND(CHAR(133),A1,1)),(IF(ISERROR(FIND(CHAR(46),A1,1)),LEN(A1),FIND(CHAR(46),A1,1)-1)),FIND(CHAR(133),A1,1)-1)))

I am using the LEFT function which returns a specified number of characters starting at the left of the target cell. Most of this monster formula is just finding how many characters that needs to be returned.

The DOW Drops Below 7,500

Go lower!!! I am not a homeowner and I don’t have any money in the stock market, but I probably will as soon as I get another stimulus check. If you’re worried, just stop! Things can only go up from here.....unless we see the DOW dip below 7,000. I don’t know what it all means, but I do enjoy the reading about all the drama.

http://finance.yahoo.com/news/Stocks-slide-as-Dow-hovers-apf-14415230.html

Minus Minus vs. *1 or ^1 or /1

'
Some operations in Excel will give you a simple “true” or “false.” For example:

=(A1=0)

Either ‘A1’ is equal to zero or it is not. So, you will get the actual words “TRUE” or “FALSE” in the cell. These kinds of operations are useful in long formulas if they are converted to their number counterparts; that is, 1 = true and 2 = false.
On the message boards, it is common practice to use minus minus to make this word-to-number conversion. For example:

=--(A1=0)

For those in the know it makes sense, but I think there is a better way. I like to use “*1.”

=(A1=0)*1

Multiplying by 1 gives the reader of your formula a better idea of what is going on and eliminates the weird spacing that the minus minus creates.