Excel Tips & Tricks ITT

[Fingolfin]

Plugged this in and noticed one correction the logical test needs to be >2, not >1. For the strings where there is one colon the logical test equals 2

Great formula dude!

Perfect! Glad you could adapt it to the known data conditions.

I had to parse a massive JSON string last month so had a lot of practice with this sort of stuff. Fortunately it was in SQL which has better string functions, but same idea.

And yes, I know there are SQL JSON functions in SSMS 17 but I was running 14.

 

[mcaulifww]

Perfect! Glad you could adapt it to the known data conditions.

I had to parse a massive JSON string last month so had a lot of practice with this sort of stuff. Fortunately it was in SQL which has better string functions, but same idea.

And yes, I know there are SQL JSON functions in SSMS 17 but I was running 14.

Yea I definitely would have preferred to run this through SQL, we have a translation formula built out already, but we dont have this data set in our data warehouse so it would be a pain to load it up just for that one formula

 

[Fingolfin]

Yea I definitely would have preferred to run this through SQL, we have a translation formula built out already, but we dont have this data set in our data warehouse so it would be a pain to load it up just for that one formula

Talk dirty to me.

Do you have SAS?

 

[mcaulifww]

Talk dirty to me.

Do you have SAS?

Unfortunately I'm not that sexy. I only started using SQL about 5 months ago. Nothing too complex, a few queries, joins, mostly just grabbing reports from our warehouse to fiddle with in excel

 

[neorunner]

 

[mcaulifww]

Couple problems with this
1) it isn't automated
2)it would replace my source field, I want to
Keep the original and have the updated field in a separate column
3) this data could be in other fields so I wouldn't want to replace things in different columns

 

[neorunner]

Couple problems with this
1) it isn't automated
2)it would replace my source field, I want to
Keep the original and have the updated field in a separate column
3) this data could be in other fields so I wouldn't want to replace things in different columns

1) Assumption the dataset is static, if you're running a macro to import data this can be macroed as well
2/3) find and replace on selected area only w/ Find & Select Go To and a named range or just use B:B

 

[neorunner]

It's friday, my head hurts and I can't figure this out.

Using the beer trade as an example, I need to take an alphabetical list of username beer swaps such as this:

Josh gives Smirinoff ICE to Trady
Justin gives Handy to Josh
Trady gives BA PBR to Justin

And sort them so it looks like:
Josh gives Smirinoff ICE to Trady
Trady gives BA PBR to Justin
Justin gives Handy to Josh

The kicker: sometimes someone gives something but does not receive anything back. So they are in the left data set but not the right. How do I re-loop into the data set that is tracing through but not in a sequential alphabetical order?

=iferror(INDEX(A$3:A$52,MATCH($K3,A$3:A$52,0)),index(A$3:A$52,MATCH(H3,A$3:A$52,0)+1,0))

This was my thought but the increment on the second index ends up hitting something already covered and going into a loop. Is there a conditional check I can put in there that grows as the lookups are done to check if it was already addressed and then how do I find a new item in the list?

 

[BadJustin]

In for the BA PBR but not the handy, sorry

 

[thrashD]

First day back from vacation and I have to deal with some date bullshit in Excel and iits the end of the day.

So, I have two columns with a beginning and ending date and timestamp. a cell will looks something like this:

"MM/DD/YYYY h:mm"

Question 1...can i calculate the begin/end difference? My thought is no, so I tried using a =LEFT/RIGHT function to separate the date and time. but some of the dates are formatted "M/D/YYYY" and "MM/DD/YYYY", which makes that difficult

so,

Question 2...I have reformatted all the dates to be consistent but when I perform said =LEFT/RIGHT function, i am given a crappy numeric value as the output and I cannot reformat that result to equal the time or date.


i hate anything date/time related in excel.

 

[Fingolfin]

First day back from vacation and I have to deal with some date bullshit in Excel and iits the end of the day.

So, I have two columns with a beginning and ending date and timestamp. a cell will looks something like this:

"MM/DD/YYYY h:mm"

Question 1...can i calculate the begin/end difference? My thought is no, so I tried using a =LEFT/RIGHT function to separate the date and time. but some of the dates are formatted "M/D/YYYY" and "MM/DD/YYYY", which makes that difficult

so,

Question 2...I have reformatted all the dates to be consistent but when I perform said =LEFT/RIGHT function, i am given a crappy numeric value as the output and I cannot reformat that result to equal the time or date.


i hate anything date/time related in excel.

Use the DATEDIF function. Every datetime field in Excel is numeric behind the scenes.

 

[thrashD]

Use the DATEDIF function. Every datetime field in Excel is numeric behind the scenes.

i don't seem to have that?

one of my first steps was searching and playing around with some of the date functions, but that was one not one of them

i know that date/time is numeric behind the scenes, but i don't normally work with date/time and it always seems to trip me up a little but usually it's a quick fix.

Would DATEDIF work with both date and time in the same cell? or do i have to curse the soul who decided to combine them in one cell?

 

[lambandtunaphish]

i don't seem to have that?

one of my first steps was searching and playing around with some of the date functions, but that was one not one of them

i know that date/time is numeric behind the scenes, but i don't normally work with date/time and it always seems to trip me up a little but usually it's a quick fix.

Would DATEDIF work with both date and time in the same cell? or do i have to curse the soul who decided to combine them in one cell?

I’m really not familiar with how time works in excel , but I would just create a new date field to strip time out
=date(year(a1),month(a1),day(a1))

 

[silverstreak]

i don't seem to have that?

one of my first steps was searching and playing around with some of the date functions, but that was one not one of them

i know that date/time is numeric behind the scenes, but i don't normally work with date/time and it always seems to trip me up a little but usually it's a quick fix.

Would DATEDIF work with both date and time in the same cell? or do i have to curse the soul who decided to combine them in one cell?

You should be able to space delimit your field into two columns, one with a date and another with the time, if that helps

 

[Fingolfin]

i don't seem to have that?

one of my first steps was searching and playing around with some of the date functions, but that was one not one of them

i know that date/time is numeric behind the scenes, but i don't normally work with date/time and it always seems to trip me up a little but usually it's a quick fix.

Would DATEDIF work with both date and time in the same cell? or do i have to curse the soul who decided to combine them in one cell?

You might need to enable some other functionality.

I’m really not familiar with how time works in excel , but I would just create a new date field to strip time out
=date(year(a1),month(a1),day(a1))

Try this, then just take the difference between the newly calculated date fields.

 

[quirkzoo]

New hire at work, during training he blew my mind. Usually I would drag a formula down to the bottom of my data set and think, “there must be a better way”.

There is.

He double clicked the little plussy thing.

Mind. Blown.

 

[beerherder]

i don't seem to have that?

one of my first steps was searching and playing around with some of the date functions, but that was one not one of them

i know that date/time is numeric behind the scenes, but i don't normally work with date/time and it always seems to trip me up a little but usually it's a quick fix.

Would DATEDIF work with both date and time in the same cell? or do i have to curse the soul who decided to combine them in one cell?

A couple ways to do it:

Create a new column, format as short date, reference your date/time column with a formula like below:
=ROUNDDOWN(A1,0)

Or if it's a one-time activity on a static dataset go to Data > Text to Columns and delimit by space.

 

[beerherder]

New hire at work, during training he blew my mind. Usually I would drag a formula down to the bottom of my data set and think, “there must be a better way”.

There is.

He double clicked the little plussy thing.

Mind. Blown.

You can also CTRL-click the plussy thing, and get a menu of a bunch of options for filling down. Great if you have to populate a pattern 1000s of times (for example, you want to fill a column starting with 1/1/2010 to 12/31/2025 and don't feel like dragging for 5 minutes )

 

[thrashD]

I’m really not familiar with how time works in excel , but I would just create a new date field to strip time out
=date(year(a1),month(a1),day(a1))

success! that help stripped out the date

You might need to enable some other functionality.

Try this, then just take the difference between the newly calculated date fields.

and double success. The difference is the time and now i have the two attributes separated so i can now do some simple date calculations.

appreciate all the help Fingolfin and for the assist on stripping out time lambandtunaphish

 

[thrashD]

A couple ways to do it:

Create a new column, format as short date, reference your date/time column with a formula like below:
=ROUNDDOWN(A1,0)

Or if it's a one-time activity on a static dataset go to Data > Text to Columns and delimit by space.

Going to try this out as well later to see what works better in case i have to work more with date/time attributes in any upcoming ad-hoc work. appreciate the tip!