James is looking for a Keystroke to enter the Exact Current Time, to the Second, in a Cell. You would think this would be a 30-second podcast, but you would be wrong. Today, a small Macro to improve the Ctrl+Colon Shortcut. Follow along with MrExcel in Episode #1724 as he shows us how to create a very simple Macro that is activated by easy Keystrokes to Enter Exact Time. *If you've been curious about VBA and would like to try it, but want something simple, open up a new Workbook and try this one yourself! This is a simple project and a rewarding one as well.
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Use Excel® 2013 VBA and Macros to automate virtually any routine task, and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! You'll discover macro techniques you won't find anywhere else and learn how to create automated reports that are amazingly powerful and useful. VBA and Macros Excel 2013
For more information on Excel 2010 VBA and Macros, check out...
VBA and Macros: Microsoft Excel 2010 - The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. Using Microsoft Excel 2010 VBA scripting features, Excel users can save dozens -- or even hundreds -- of hours per year. But most Excel users have never written a VBA script: many haven't even used Excel's built-in Macro Recorder. VBA and Macros: Microsoft Excel 2010
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1724 - Exact Time to the Second via Keystroke!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question, sent by James T. at YouTube, he says: "In columns A-D, when I push this button", he wants to make the exact time to the second to appear in that cell.
They use it to calculate how long a call is.
Well James, this is easy, it's just Ctrl+: or Ctrl+Shift"; I'll put the current time, and yeah, it's showing you 4:22.
But if we would format these columns to show time down to the second, of course, it would work.
So choose that one, click OK, and then over here, Ctrl+Shift+', press Enter, and- Oh, oh.
OH!
I see the problem!
OK, so my shortcut key of Ctrl+: doesn't put in the seconds, you need something to put in the seconds.
Let's switch over to VBA for that, Alt+F11.
Let's see, if you want it in your personal macro workbook, we could just put it right here in this module, or insert a module.
I'm going to put it in the workbook for Podcast1724.
So click there, Insert, Module, and let's see, let's call it Sub ExactTime, it's going to be really, really simple: It's going to be ActiveCell.Value = Time.
VBA gives us this two system variables, Time and Datetime, and date will give us the exact time, and it'll do it to the second.
Let's, just in case the cell is not formatted, I'll say NumberFormat = "h:mm:ss", that will give us military time.
If you wanted kind of normal time, you can go AM/PM, but I'm going to go with the military time, alright.
Yeah, let's switch back to Excel.
I'll do Alt+F8 so I can assign a shortcut key.
So here's our ExactTime, Options, I'm going to use T for this, Ctrl+T is already table, so you might want to put a Shift+T in there, choose something else, something that's easy to hit.
Click Cancel, try Ctrl+T, and puts the exact time.
And I'll use the arrow key to go over, Ctrl+T, Ctrl+T, Ctrl+T.
OK, you know what, for James, I thought we could make this easier, thought we could have it automatically- Of course, we could do it, we could have it automatically go over to the right.
So we're going to say, let's see, if ActiveCell.Column < 4 then, we want to go to the right, so ActiveCell.Offset.
This is not as confusing as the Excel function OFFSET, this is VBA.
There I go down 0 rows, and 1 column over.
.Select, and then, otherwise, if we're already over in column 4, then we want to do Cells.ActiveCell.Row + 1, back to column 1,.
Select.
End If.
There we go.
Alright, so this is kind of the James' addendum, because James wants to be entering data in A-D.
So Ctrl+T, Ctrl+T, Ctrl+T, oops, I missed, Ctrl+T, Ctrl+T, and there we go.
So, I guess phone starts to ring, call gets picked up, someone goes on with your question, press Ctrl+T again, and then I don't know what the 4th column is.
But, very easy way now to log that data, and then later on come back, and do some sort of analysis with it.
Alright, I want to thank James for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1724 - Exact Time to the Second via Keystroke!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
And today's question, sent by James T. at YouTube, he says: "In columns A-D, when I push this button", he wants to make the exact time to the second to appear in that cell.
They use it to calculate how long a call is.
Well James, this is easy, it's just Ctrl+: or Ctrl+Shift"; I'll put the current time, and yeah, it's showing you 4:22.
But if we would format these columns to show time down to the second, of course, it would work.
So choose that one, click OK, and then over here, Ctrl+Shift+', press Enter, and- Oh, oh.
OH!
I see the problem!
OK, so my shortcut key of Ctrl+: doesn't put in the seconds, you need something to put in the seconds.
Let's switch over to VBA for that, Alt+F11.
Let's see, if you want it in your personal macro workbook, we could just put it right here in this module, or insert a module.
I'm going to put it in the workbook for Podcast1724.
So click there, Insert, Module, and let's see, let's call it Sub ExactTime, it's going to be really, really simple: It's going to be ActiveCell.Value = Time.
VBA gives us this two system variables, Time and Datetime, and date will give us the exact time, and it'll do it to the second.
Let's, just in case the cell is not formatted, I'll say NumberFormat = "h:mm:ss", that will give us military time.
If you wanted kind of normal time, you can go AM/PM, but I'm going to go with the military time, alright.
Yeah, let's switch back to Excel.
I'll do Alt+F8 so I can assign a shortcut key.
So here's our ExactTime, Options, I'm going to use T for this, Ctrl+T is already table, so you might want to put a Shift+T in there, choose something else, something that's easy to hit.
Click Cancel, try Ctrl+T, and puts the exact time.
And I'll use the arrow key to go over, Ctrl+T, Ctrl+T, Ctrl+T.
OK, you know what, for James, I thought we could make this easier, thought we could have it automatically- Of course, we could do it, we could have it automatically go over to the right.
So we're going to say, let's see, if ActiveCell.Column < 4 then, we want to go to the right, so ActiveCell.Offset.
This is not as confusing as the Excel function OFFSET, this is VBA.
There I go down 0 rows, and 1 column over.
.Select, and then, otherwise, if we're already over in column 4, then we want to do Cells.ActiveCell.Row + 1, back to column 1,.
Select.
End If.
There we go.
Alright, so this is kind of the James' addendum, because James wants to be entering data in A-D.
So Ctrl+T, Ctrl+T, Ctrl+T, oops, I missed, Ctrl+T, Ctrl+T, and there we go.
So, I guess phone starts to ring, call gets picked up, someone goes on with your question, press Ctrl+T again, and then I don't know what the 4th column is.
But, very easy way now to log that data, and then later on come back, and do some sort of analysis with it.
Alright, I want to thank James for sending that question in, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
