Whilst working on a side-project with work, I needed to create a form that could easily allow people to add the current time, date and time zone to a spreadsheet.
Date and time is easy to add within Excel using the NOW() function and a little bit of VBA scripting. However, I also needed the timezone inserting in to the form, as it will be used in multiple regions around the world.
Now, I could have simply added an extra column and got people to type their timezone in manually, but I’m of the thinking that if something can be automated, it should be.
By VBA skills leave a lot to be desired, but after spending a couple of hours doing some research (and a little help from a developer friend of mine), I finally worked out a way of inserting the current time, date and system timezone in to a cell, with a simple double-click of the mouse.
If you don’t want, need, or care about the technicalities of getting this to work, you can download this example spreadsheet.
Note: you will need to enable macros before the time stamp double-click will work. There is no malware embedded in the spreadsheet (MD5 at the bottom of this article).
If you want to integrate this tool in to a spreadsheet you’re already using, here is a simple way of achieving that goal:
View Code. This will open the Visual Basic editor.
double-click.txtin to the blank screen that appears.
B3:B10000to whatever range of cells you want your double-click time stamp to work on.
File > Import Fileand import
CTime.clsfrom the package you just downloaded.
Module3.basfrom the same package.
Saveand ensure you save your spreadsheet as a
Macro Enabled Spreadsheet. Once that’s done, your VBA window should look something like this:
At this point, you should be able to return to your spreadsheet, double-click within the range of cells that you selected in step 3 above, and the current time stamp with system time zone should appear within the cell.
Thanks to http://www.cpearson.com for the original code.