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).
Setting It Up
If you want to integrate this tool in to a spreadsheet you’re already using, here is a simple way of achieving that goal:
- Open the spreadsheet you want to integrate the time zone tool in to, then right click on the sheet tab at the bottom of the screen and select View Code. This will open the Visual Basic editor.
- Download this package and paste the contents of double-click.txt in to the blank screen that appears.
- Change line 2 where it says “B3:B10000” to whatever range of cells you want your double-click time stamp to work on.
- Next, click on File > Import File and import CTime.cls from the package you just downloaded.
- Repeat step 4 for Module2.bas and Module3.bas from the same package.
- Click Save and 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.
- Timestamp.zip MD5 – 72416e349c69ac397d718a5ed29b562d
- VBA-Code.zip MD5 – f05865c42dda92d5e9471375ab698214