I’ve set up an Excel spread sheet as a daily work progress manager with cells for the customer and vehicle details, time required and where in the garage their car is (work in progress, at MOT station, Valet bay, completed,etc) and I’ve set it up as a shared Excel document, which autosaves every five minutes and shows who has made changes and when, as others can update it at the same time.
It all works fine, but one thing I want to do is get the “time out” cells to turn red or flash when the time out is overdue. I’m sure it will be something along the lines of =IF(NOW()> A3,then A3starts flashing, else do nothing), but I don’t know how to make the cell flash. I know you can have blinking text in Word, but I’ve never tried it in Excel. Any ideas?
a timer and alternate cell.backcolor ?
I don’t know how you would invoke a refreshing akaa (timer)
But conditional formatting would prob do what your after with a simple if value < time blah blah blah make the cell red
Conditional formatting will only work if the spreadsheet is refreshed. You say you refresh the dataset every 5 minutes, but does the screen refresh for the user?
Timer events would allow the data to be refreshed but on a spreadsheet well lets say not a good idea. I would try this in ACCESS maybe. You could have a query fire off every 5 minutes and show the items near/at time.
You should remember that for the spreadsheet to work you must refresh the complete data…while in a DB your query is the fresh tool.
Yes, it updates and points out any changes made by others accessing the workbook. You get a little comment marker in the corner of any changed cells and this tells you which user modified the cell. Quite a neat little touch that I wasn’t aware of when I set it up initially.
The only other issue I have with time, is that the NOW() function uses date and time and not time specifically. I haven’t found a formula that I could use that does. I think the only way I could do it was with a nested IF formula, but I’m struggling with the time and date aspect, having nothing easy to subtract from something else.
Format the now cell to be Time only format
Use a new cell and formula lookup that cell and e.g. + time
e.g. =A1 + TIME(1,35,10) = look at cell a1 and add 1hr 35 mins to it.
Depends on exactly what your after else just a simple format of cell will do to be time only.
keep one cell as your Now() formated as time then use 2nd cell formatted as time for when required then A-B
/edit … Now your problem could be that excel does not work with negative times therefore…
Two cells formatted to time
And formula compare = … =IF(A1>B1,B1+1-A1,B1-A1) that will calculate a time difference between the two times.