Discussion General Discussion

[HoleyFrijole$]

Sorry guys. No luck. Because it is for example 1/1 the spreadsheet automatically assumes it is the 1st of Jan. I've tried text to columns with the backspace as a separator but it still assumes a date when it splits. I've tried changing it to text and it does something else giving me a number in the thousands.

I'll get you a formula

 

[Goodie's Guns]

Sorry guys. No luck. Because it is for example 1/1 the spreadsheet automatically assumes it is the 1st of Jan. I've tried text to columns with the backspace as a separator but it still assumes a date when it splits. I've tried changing it to text and it does something else giving me a number in the thousands.

The do around, albeit a bit painful, is to put a comma in front of each of the FK tallies.

e.g. ‘1/1

 

[freowho]

The do around, albeit a bit painful, is to put a comma in front of each of the FK tallies.

e.g. ‘1/1

Can you do it in one hit? The opposite of text to column?
Edit: Actually it wouldn't work. What it's done is automatically assume a date and the data is now shown as 1/1/2021.
It's a very mild annoyance so I'd rather leave it than do anything to complicated.

 

[Goodie's Guns]

Can you do it in one hit? The opposite of text to column?

With a & formula combining the comma and FKs you probably can. I’d need to be on my laptop and excel to try and come up with/test, but I’m only on phone atm.

 

[HoleyFrijole$]

Sorry guys. No luck. Because it is for example 1/1 the spreadsheet automatically assumes it is the 1st of Jan. I've tried text to columns with the backspace as a separator but it still assumes a date when it splits. I've tried changing it to text and it does something else giving me a number in the thousands.

=IF(M5="",0,IF(MID(M5,2,1)="/",LEFT(M5,1),DAY(M5))) for free's for (where column M is ff/fa)
=IF(M5="",0,IF(MID(M5,2,1)="/",RIGHT(M5,1),MONTH(M5))) for free's against (where column M is ff/fa)

 

[Ironhawk]

I have a spreadsheet question. When I copy the fanfooty stats onto a spreadsheet it converts the free kicks into dates. Any tips to get it back to it's original format?

Highlight the column, format cells, choose Custom in the Number tab and type d/m as the format.

 

[HoleyFrijole$]

This will be correct assuming less than 10 free kicks for or against - not sure that's happened before

 

[freowho]

Highlight the column, format cells, choose Custom in the Number tab and type d/m as the format.

Winner, winner, chicken dinner. Haven't checked the whole lot but that will do.

Thanks for all the tips.
I've actually learnt more about spreadsheets from SCS than any workplace I've ever been in.

 

[HoleyFrijole$]

Winner, winner, chicken dinner. Haven't checked the whole lot but that will do.

Thanks for all the tips.
I've actually learnt more about spreadsheets from SCS than any workplace I've ever been in.

my excel spreadsheet by the end of last season was 45M haha

 

[freowho]

my excel spreadsheet by the end of last season was 45M haha

I assume you're talking storage. I thought mine was decent at 877KB but clearly I am a grasshopper with much to learn.

 

[Ironhawk]

Winner, winner, chicken dinner. Haven't checked the whole lot but that will do.

Thanks for all the tips.
I've actually learnt more about spreadsheets from SCS than any workplace I've ever been in.

Glad to help.

I'm not as proficient as a lot here with Excel, but I can do a lot of the basics. I've had a similar issue previously and knew that you can generally force formats that way. As mentioned as long as the counts are all under 10 frees for/against it should work, you may need to manually edit it if it ever goes into double digits.

 

[HoleyFrijole$]

I assume you're talking storage. I thought mine was decent at 877KB but clearly I am a grasshopper with much to learn.

ended up building a simulator to give me 100 different variants of how umpires may give out brownlow votes for every game. Starts to slow down after a while...

 

[Connoisseur]

https://twitter.com/i/web/status/1369812555741294595

 

[Beg2Differ]

I have a spreadsheet question. When I copy the fanfooty stats onto a spreadsheet it converts the free kicks into dates. Any tips to get it back to it's original format?

I see you have been provided with a few solutions here however there is one more that gives you a slightly better outcome. Prior to pasting into excel if you format the relevant column as text then it won't convert it to a date when you paste as text.

Select column (or range)>format cells>text>ok

• @Ironhawk suggestion will give you a visually satisfactory outcome however the underlying entry is still stored as a number. That means if you wanted to perform any type of operation on that column then you will get unreliable results because the ones that can be converted to sensible dates will be and the ones that can't won't so you will get a mix of dates and text.
• @HoleyFrijole$ suggestion to use a formula will give you the same result to my suggestion above but requires a 3 step process. Paste data>create formula>copy & paste special as values. However there is a simpler formula option as follows.
  
  =IF(K3="","",TEXT(K3,"d/m"))
  
  Where k3 is the cell with the date or text in it.
  Note that it will work regardless of whether the paste converts to a date or not
  The IF bit is just to test for empty cells because the text formula will give you the wrong result for those cells.

 

[freowho]

I see you have been provided with a few solutions here however there is one more that gives you a slightly better outcome. Prior to pasting into excel if you format the relevant column as text then it won't convert it to a date when you paste as text.

Select column (or range)>format cells>text>ok

• @Ironhawk suggestion will give you a visually satisfactory outcome however the underlying entry is still stored as a number. That means if you wanted to perform any type of operation on that column then you will get unreliable results because the ones that can be converted to sensible dates will be and the ones that can't won't so you will get a mix of dates and text.
• @HoleyFrijole$ suggestion to use a formula will give you the same result to my suggestion above but requires a 3 step process. Paste data>create formula>copy & paste special as values. However there is a simpler formula option as follows.
  
  =IF(K3="","",TEXT(K3,"d/m"))
  
  Where k3 is the cell with the date or text in it.
  Note that it will work regardless of whether the paste converts to a date or not
  The IF bit is just to test for empty cells because the text formula will give you the wrong result for those cells.

Thanks. I'll be coming back to this advice the next time I have the same problem.

 

[Ironhawk]

I see you have been provided with a few solutions here however there is one more that gives you a slightly better outcome. Prior to pasting into excel if you format the relevant column as text then it won't convert it to a date when you paste as text.

Select column (or range)>format cells>text>ok

• @Ironhawk suggestion will give you a visually satisfactory outcome however the underlying entry is still stored as a number. That means if you wanted to perform any type of operation on that column then you will get unreliable results because the ones that can be converted to sensible dates will be and the ones that can't won't so you will get a mix of dates and text.
• @HoleyFrijole$ suggestion to use a formula will give you the same result to my suggestion above but requires a 3 step process. Paste data>create formula>copy & paste special as values. However there is a simpler formula option as follows.
  
  =IF(K3="","",TEXT(K3,"d/m"))
  
  Where k3 is the cell with the date or text in it.
  Note that it will work regardless of whether the paste converts to a date or not
  The IF bit is just to test for empty cells because the text formula will give you the wrong result for those cells.

I figured it may have been too late for him to change the formatting, but you are right, that first option is the neater method. I had a feeling there would be even better options to do it, as per the second one. I wasn't aware there would be issues with using those cells later in formulas etc with my method, as I didn't think it would be possible to manipulate. I guess you could create a formula to split out a character from the text string, and use that if you wanted...

As I said, I'm an Excel rookie compared to some here

 

[Ben's Beasts]

The sub could be returning in 2021...

"THE AFL is strongly considering a request from some coaches to use a substitute player to replace a concussed teammate in 2021 matches.
The mooted change would need to be officially approved by the AFL Commission before Thursday's night's Richmond-Carlton season-opening match at the MCG.
Coaches have asked for a additional 23rd player to be the concussion sub. Currently, each team is comprised of 18 on-field players and four interchange."

If it does come into effect here's hoping that the 23rd player is named when the teams are announced the day before, not when final team lineups are announced 45 mins before the game..

Also, I wonder what it will mean for SC. Would the 23rd player's score only be counted if they are subbed on? Otherwise they just count as a DNP. You would think that would be the case..

https://www.afl.com.au/news/561189/coaches-concussion-worry-sparks-push-for-23rd-player

 

[jaca]

"Concussion sub" would avoid some of the issue mind, as they'd be hoping not to use him (I would think having a mandatory concussion protocol would dissuade from fake claims?)

 

[Herbie66]

And in case a team (s) have/has more than one player with concussion during the game do they then have a 24th , 25th etc player player.

Be interesting how they police it , are they able to use the sub while the player is being assessed (ie 20 minutes) or have to wait until they are officially ruled out ?

 

[Diabolical]

Love it ... I know footy season is close when it takes me half the evening just to catch up on all the posts on this site that have been made today!