Forum Discussion
Formula Help
I have created a spreadsheet for driver sign-on for morning and afternoon buses.
I have route number, bus number, driver name, expected
sign-on time, actual time signed on field, and a signed-on
field.
I have done conditional formatting so when a driver signs on, the user puts a Y in the signed-on field. That then changes from red to green to show that the driver is out on route.
I want the actual time signed on field to automatically record the time that the user adds the Y to the signed-on field.
I have the following formula
=IF(F7="Y",MOD(NOW(),1),"")
This inserts the time into E7.
My issue is that if I then put Y into, say, F14 five minutes later, it changes the time in E7 to the current time.
What should my formula be so that the E column times do not change when another driver is logged on?
Thanks for your help and suggestions :)
4 Replies
As mathetes said, cell formulas get updated so this isn't the best approach. There is a way using circular referencing and then changing the sheet to allow circular referencing but I can't say I would recommend that. The best alternative (IMHO) is to just have them type CTRL-: (which is CTRL-SHIFT-;) instead of 'Y', which will automatically insert the current time. It is nearly as easy as typing the Y and if you 'train' them and maybe have a few queues on the sheet they would learn quickly. You can even add Data Validation to that column to only allow time and have the error message remind them to use CTRL-SHIFT-;
As mathetes also mentioned you can use a macro but I would push towards using a Script instead as Scripts can be used in the desktop app and in the browser and mobile apps. The next question is if they are logging in on their own device or do you have a central station they use and if the file is in sharepoint or just on a local or company drive. Because if it is in sharepoint and they each have their own account they use then the script could even detect the user account and either use that to determine which line to check-in or log.
Another good/better approach would be to create a excel form (it is really a microsoft form associated to the excel sheet) but basically they can go to that form and click check-in. That form would then log into the excel sheet the user and the time and then you can query that log for if and when they logged-in. If they each have their own MS account you can have it use that account to log who, but if not you can have a field on the form be for them to select their name or ID. If you want to try this approach go to Insert -> Forms -> New Form. note this form will just create a log of entries into the excel sheet so you then need to do a lookup to determine if there is an entry for John Doe today and pull the time from that entry.
I'm happy to help further but need to understand more about the items mentioned above (is this is sharepoint, do they each have their own account, do they log in using their own account or at a central station, do they need to enter any other information or just check-in./out, etc....)
I would have used "Script" and "Macro" as interchangeable terms. Since I know how to do neither (I did a couple decades ago know how to write visual Basic, I think it was) and for the sake of other relative novices, what's the difference, and under what circumstances is each "favored"?
So I recently did a short tips & tricks class on the differences and comparing Macros (VBA), Scripts, and LAMBDA functions. I know YOU know LAMBDA very well but I include it here for others. So in a nutshell:
Macros are based in VBA language, built into Excel, are extremely powerful and can run autonomously in the background (monitor for sheet changes and such) or be executed (e.g. can be run using a button) but only work in desktop Excel and must have security permissions turned on.
Scripts are the newer way you execute more complicated functionality, but limited compared to VBA (e.g. you can't access the OS and other files). It runs in the cloud, can only be executed (e.g. via a button on the sheet), it can be slow/delayed due to it being in the cloud, but it works in basically all versions of excel including desktop app and in the browser.
LAMBDA functions are excellent options for replacing many of the old UDF (user defined functions) that were in VBA. As long as the function can be written using standard in cell functions, you can embed it in a LAMBDA function in the Name Space and be able to call it anywhere in the sheet.
Here is a +/- sheet I created for them:
VBA details:
+ Very powerful
+ On-click, on-change, on-close, on-open, etc…
+ OS and filesystem manipulation
+ Change actual values
+ Change formatting
+ Change workbook settings
- turned off by default
- only supported in desktop (not online, not mobile)
- some workplace filters/rules prevent
MS scripts
- Only run on action (i.e. on button click)
- Run in the cloud (but could be a +)
+ Compatible on all platforms
+ Change formatting
+ Change actual values
+ Change some workbook settings
- No OS/filesystem access
- Script language very different than VBA
- Technically NOT a UDF since can't be in-cell
LAMBDA
+ Built into excel so more efficient
+ Uses familiar excel 'programming' context
+ Auto updates results
- Can NOT change values (only display new result)
- Can NOT change formatting
- Can NOT change workbook settings
- Can NOT access file system (except limited information through use of INFO and CELL functions which are not supported in all platforms)
- Limited calculation to workbook functionality (limited arrays, looping, etc…)
+ Recursion IS allowed
So when would I recommend one vs another?
LAMBDA - I would recommend using this any time it is possible to achieve what you want/need.
MACRO/VBA - I would ONLY use this if it is something only I or the intended user, or other very limited target would be using and understanding that it will only work using desktop app and I can't achieve the needed output/functionality using LAMBDA or Scripts.
SCRIPTS - This gives you the ability to actually make changes to the sheet/values/formating in a prescribed way/function. For example you always import data and then have to do X,Y,Z to format it the way you like, a script might be great for that. Scripts can also be link to and executed by Microsoft Flows so you can have either other actions in the Sharepoint universe trigger an action or just have it as a scheduled event. For example I have a flow that will run a script on an active workbook each day to do some checks for some common errors and then return any potential errors to the flow, which will then email me what those errors are and I can hop into the sheet to make correction and/or notify the user that made the errors and ask them to fix it.
I hope that helps :)
How precise do these times need to be? Are the drivers paid by the minute? Do you trust them to enter a time, rather than have it entered based on logging in?
There certainly are macros that could be written (though not by me) to simply record the time when X happens. :That is to say, it's not a formula (as you've discovered), since formulas get evaluated every time something happens.
But short of a macro, depending on the level of precision and whether or not you trust your drivers, you could develop a drop down from which the drivers select a start (and finish?) time to the closest 5, 10, or 15 minutes. Once selected, that would stay.
Before we show you how to do it, though, we'd need the answers to my first set of questions.. Or you can ask for a macro, and I'll leave that to those who know how to write macros.
