Spreadsheet tip

  • 2
  • Idea
  • Updated 2 years ago
  • Not Planned
I've been doing a lot of formatting and manipulation of spreadsheets to produce data as required for my displays and I have a little tip for those who may be showing sport fixtures on their displays.

One of the sheets I use is a fixture list of football (soccer for our colonial cousins) teams where I want to display the Home team, game date and time and their opponents. See the sample sheet below.



To get the next game from the list I use this simple function

=IF(DATEVALUE(A2)>Today(),E2&" at "&B2&" pm on "&A2,if(DATEVALUE(A3)>TODAY(),E3&" at "&B3&" pm on "&A3,if(DATEVALUE(A4)>TODAY(),E4&" at "&B4&" pm on "&A4,if(DATEVALUE(A5)>TODAY(),E5&" at "&B5&" pm on "&A5,if(DATEVALUE(A6)>TODAY(),E6&" at "&B6&" pm on "&A6)))))

This interrogates the Date cells in order and checks if the date is greater than Today - if TRUE it puts the data from the corresponding AWAY team into cell I2 plus it adds the text " at " and the data from the Time column plus " pm on " and the data from the Date column..

If the first date is TRUE the query stops otherwise it checks the next Date and if TRUE it stops and so on through the list.

To ensure the spreadsheet is updated so that the Today() function is tested automatically and the result updated, I set the attributes of the sheet to 'Recalculation = On change and every hour'. This can be found under 'File' - 'Spreadsheet Settings'

So now I can set my clients display to automatically update the fixture from the contents of cells G2 to I2 without their intervention or needing to edit.

It can be a bit cumbersome to set the calculation when the fixture list at the beginning of the season is going to be about 40 rows - perhaps do it for 4 or 5 weeks at a time so only needs an edit once a month.

Hope this helps some of you
regards,
Jim Coyle
Photo of Jim Coyle

Jim Coyle, Champion

  • 1,494 Points 1k badge 2x thumb
  • proud of myself

Posted 3 years ago

  • 2
Photo of Ashleigh

Ashleigh

  • 3,740 Points 3k badge 2x thumb
Thanks for sharing Jim!

I know there's a lot you can do with spreadsheets using functions, but it's been pretty unfamiliar territory for me personally. I'd be interested to see what our other users may be doing to streamline their data better.

Ashleigh
Photo of Jim Coyle

Jim Coyle, Champion

  • 1,494 Points 1k badge 2x thumb
Thanks Ashleigh.

A little refinement to the calculation:
=IF(DATEVALUE(A2)>Today()-1,E2&" at "&B2&" pm on "&A2,if(DATEVALUE(A3)>TODAY()-1,E3&" at "&B3&" pm on "&A3,if(DATEVALUE(A4)>TODAY()-1,E4&" at "&B4&" pm on "&A4,if(DATEVALUE(A5)>TODAY()-1,E5&" at "&B5&" pm on "&A5,if(DATEVALUE(A6)>TODAY()-1,E6&" at "&B6&" pm on "&A6)))))

The Today() function is modified by adding minus 1(-1) so that the fixture is still displayed when the fixture is True on the current date or in the future.

Without this minor change the date is not greater than the current date and on the day of actual play the fixture would be showing the next in line. The fixture now updates on the following day.

There are probably much more elegant ways of doing this but it works for me and is simple to follow

regards
Jim Coyle
Photo of Ray Durkin

Ray Durkin

  • 3,480 Points 3k badge 2x thumb
Jim,

This is great - thanks very much - I'm bookmarking this to refer other Rise users here as I have been asked how to do this in the past more than once.
Photo of Jim Coyle

Jim Coyle, Champion

  • 1,494 Points 1k badge 2x thumb
Thanks Ray - I'm sure there a lot of users who display sport fixtures or similar event listings that are date dependant so hopefully this will be of use to them - plus it helps cut down the amount of updates and editing required when some of the work can be automated.

regard,
Jim Coyle
Photo of Ray Durkin

Ray Durkin

  • 3,480 Points 3k badge 2x thumb
Yes!  And class schedules, menus, and much more
Photo of Jim Coyle

Jim Coyle, Champion

  • 1,494 Points 1k badge 2x thumb
A couple more refinements to the code:

=IF(DATEVALUE(I3)>Today()-1,M3&" at "&J3&" pm on "&TEXT(I3,"dddd dd mmmm, yyyy"),if(DATEVALUE(I4)>TODAY()-1,M4&" at "&J4&" pm on "&TEXT(I4,"dddd dd mmmm, yyyy"),if(DATEVALUE(I5)>TODAY()-1,M5&" at "&J5&" pm on "&TEXT(I5,"dddd dd mmmm, yyyy"),if(DATEVALUE(I6)>TODAY()-1,M6&" at "&J6&" pm on "&TEXT(I6,"dddd dd mmmm, yyyy"),if(DATEVALUE(I7)>TODAY()-1,M7&" at "&J7&" pm on "&TEXT(I7,"dddd dd mmmm, yyyy"))))))

This produces text in the format:
"Dundee at 7:45 pm on Friday 22 January, 2016" in cell C2

Also this client provides transport to the game with pickup 45 minutes before the kickoff. An additional line of text can be produced using:

IF(DATEVALUE(I3)>Today()-1,"be here for the bus by "&TEXT(J3-time( 0,45,0 ),"h:mm")&" pm",if(DATEVALUE(I4)>TODAY()-1,"be here for the bus by "&TEXT(J4-time( 0,45,0 ),"h:mm")&" pm",if(DATEVALUE(I5)>TODAY()-1,"be here for the bus by "&TEXT(J5-time( 0,45,0 ),"h:mm")&" pm",if(DATEVALUE(I6)>TODAY()-1,"be here for the bus by "&TEXT(J6-time( 0,45,0 ),"h:mm")&" pm",if(DATEVALUE(I7)>TODAY()-1,"be here for the bus by "&TEXT(J7-time( 0,45,0 ),"h:mm")&" pm")))))

Produces:

"be here for the bus by 7:00 pm" in cell C3

Notice how the Time is reduced by 45 minutes in the code.



I am based in the UK so the formatting for date/time is set for our region - modify the above for your own purposes.

regards,
Jim Coyle
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
This is great! Thanks Jim!
Photo of Jim Coyle

Jim Coyle, Champion

  • 1,494 Points 1k badge 2x thumb
It appears there are reliability issues with the Today() function and auto updating of the sheet.

Try replacing Today() with Now() in the code snippets above

regards,
Jim Coyle
Photo of Doug Hamil

Doug Hamil

  • 60 Points
I'm new to this but I am struggling to get just my generated text to show up. Currently the entire spread sheet is visible. When I try to enter a cell range (C2) which is where my function generated text shows – nothing displays.
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Hi there Doug!

Can I ask you to remove the older Google Spreadsheet and use the new Google Spreadsheet Early Preview version? Put the same cell in both the starting and ending cell numbers for the range you want to see, and everything should start working, so your settings will look like this:


Let me know if you have any trouble!
Photo of Raul

Raul

  • 240 Points 100 badge 2x thumb
Jim,
Stumbled onto this post. The spreadsheet functionality allows calculations from the spreadsheet to pass over to the display? If so, that is really goods news to me. Just started messing with displays and was wondering about this.
Photo of Biologyben

Biologyben

  • 3,558 Points 3k badge 2x thumb
Yes - I do something similar but with a simple filter call in a separate sheet and it pulls automatically from that sheet.  The nice thing about that is you don't have to worry about expanding your formula to fit your rows.

I discussed one possibility here 

https://community.risevision.com/rise_vision_inc/topics/google_spreadsheet_form_integration_with_dat...
(Edited)
Photo of Robb

Robb, Official Rep

  • 76,676 Points 50k badge 2x thumb
Thanks BiologyBen!

Raul, did that answer your question?
Photo of Raul

Raul

  • 240 Points 100 badge 2x thumb
Sure did Robb. I will look at the post and start tinkering with my intended application.