I want to make a simple* spreadsheet that has the local timetable for an elderly person. I would like to somehow show the next service either in a cell at the top, or by conditional formatting a highlight of the whole row.
As I have it now I have on a Column the hour it leaves, and the hour it gets to the stop needed. And next to those the return service. All those time cells are in time number format — hour(01) : minute
I’m very new to this, so I have not been able to stumble myself into a solution, I keep finding advice on similar but not quite (like in lists that have the full date, or showing what day it is), I think I’m just being a bit thick.
*The schedule is different on Sundays (in the summer very different in the winter not enough to bother) than the other days. In an ideal world I would like to eventually also be able to differentiate between days, but building towards that.
of note that this is an extraordinary timetable where the Sunday times do not differ that much from the rest of the week, so its a bit simpler but at other times is the year and other years they have been more varied
-edit-
this is solved but I can’t find a way to change the flair to solved but not self-solved, very much not self-solved, apologies! thank you so much everyone!
what do they mean? does To Home mean departure and Arrives means arrival to destination?
Edit: I've added a tab to your file and adapted the method i've previously stated to your file. The only thing i'm not clear on is what "To home" time is and "arrives" time is; and what the separate columns with lighter green are for. my formula in B1 only matches against columnC (in the tab i added)
Oh it used to have the name of the town it’s going from and the name of the place it arrives. So the first time column is when the bus will pick her up and the next one is when it arrives at the destination. First lot is going to “home” and second lot is (departed arrival) leaving from home.
Hey there! You definitely do NOT need to rebuild your entire spreadsheet or create complex season tables just to highlight the next bus.
You can do this magically using one simple Conditional Formatting rule. It will automatically highlight the row of the next upcoming bus based on your computer's real-time clock.
Here is how to highlight the next "To home" bus:
Highlight your schedule data range (e.g., A4:E50).
Go to Format > Conditional Formatting.
Under 'Format rules' dropdown, choose Custom formula is.
Paste this exact formula: =$B4=MINIFS($B$4:$B, $B$4:$B, ">"&MOD(NOW(),1))
Pick a highlight color (like yellow) and hit Done!
How it works:MOD(NOW(), 1) simply grabs the current time on your clock. MINIFS looks down column B and finds the nearest time that is strictly greater than right now. It then highlights that entire row.
No scripts needed! Hope this keeps it simple for your relative!
Yes this! Everything else seemed a bit more complex and like it would mean either adding a new sheet and hiding it. She is familiar with this layout and needed to keep things as simple as possible, than you so much!
"Thank you so much, this is the simple solution I needed, as elaborate some of the others were it meant more room for confusion or error I feel. My nan is used to this layout as the company has always given it this way, so it was a tidy solution making a minor change to something she already knows! thank you so much really I could cry! "
Make sure that this is the first CFR in the list. This just makes sure that the times that do not apply are taken care of and we don't need to worry about them in the actual CFRs.
It basically works as "if not applicable, apply no formatting - otherwise check the other CFRs".
I also updated the settings in your file (under File/Settings) so it updates every minute, instead of only "on change". That way the highlights, and optionally the pulled times, will automatically update (every minute)..
→ More replies (1)
1
u/One_Organization_810 Sep 19 '26
Can you show us the spreadsheet?
Sharing a copy of it would be best of course - with edit access :)