Excel Sorcerers!

[FaultyMario]

Do you want to see me thrown out of the conjugal home?
If not, please help me get the woman's contacts back to her Moto G.

There's a phone-to-web service her carrier offered for a couple of bucks when she got her new LTE sim card. She got on board and nice and round her contacts are on the website. When the "restore to sim" function is clicked, internet magic starts but then nothing happens. Woman goes mad. Foam around the mouth and all that.

Now I've got those contacts from the website onto an Excel Spreadsheet, they're one column and it's just name and phone number. Each contact captured in a single cell.

What do I need to do to get them all into an Android contacts archive? I could split them and make multiple columns or trans-paste the information onto a template if needed.

[Kchrpm]

Easiest thing to do would be a text to columns with space delimiter, then cleanup from there, and then move into the Google Contacts import template ( https://support.google.com/mail/answer/14024?hl=en ). Then you get all those contacts and numbers synced onto Google, and you never have to worry about transferring them again.

It's hilarious that carriers still offer that, that feature has been built into every smartphone for probably a decade now, but people don't know about it or bother doing the minimal setup it takes, so they pay extra and/or have to tell everyone to text them every time they get a new phone.

[George]

Mr. Kchrpm - and any other interested challengers - I have quite possibly the most difficult Excel challenge EVAR!

Fortunately, I don't NEED to be able to do this, but it would make my life a little easier at work.

Invoices have due dates. Our company issues payments every Thursday and pays invoices with dates to pay through the following Friday. So, this week on Thursday April 9, we will pay invoices with due dates up to and including Friday April 17.

Challenge:

Given a spreadsheet of dozens or hundreds or maybe even thousands of invoices with due dates formatted as dates in one column, is it possible to add another column with some kind of formula to show the projected actual date of payment?

Example:

Due date       Projected payment date
4/7/2015       Thurs 4/9/2015
4/9/2015       Thurs 4/9/2015
4/11/2015      Thurs 4/16/2015
4/16/2015      Thurs 4/16/2015
4/18/2015      Thurs 4/23/2015

[Kchrpm]

If the due dates start in A2

=A2+choose(weekday(A2,1),4,3,2,1,0,-1,5)

[George]

HEY, THAT ACTUALLY WORKS!

Wow. Thank you, sir! This will make me THE MAN at work.

I had never heard of a "choose" function in Excel.

Mucho appreciado!

[Kchrpm]

Choose is cool, it lets you do any number of different functions, kind of like unpacking a bunch of nested if/then statements.

[George]

That makes sense. I thought the formula would be something like this one that I use to show aging buckets.

=IF(T2<=30,"1-30 DAYS",IF(T2<=60,"31-60 DAYS",IF(T2<=90,"61-90 DAYS",IF(T2<=99999,"91 DAYS OR GREATER"))))

[Kchrpm]

Yep, but since the formula to find what day of the week a date is spits out a number, usually between 1 and 7, the easiest thing to do with that is use Choose.

[thesameguy]

I just spent a half hour learning about making Excel do bell curve charts.

AIN'T NOBODY GOT TIME FOR THAT!

[Kchrpm]

Some of the fun days at work are when they ask me to figure out how to make a chart in Excel that previously they've only drawn free-hand in Powerpoint or on a whiteboard.