Learn Excel Blog

               

Felipe writes: I need to find the elapsed time between two moments. Each moment has a date and a time. I need to know how much time was elapsed, in minutes or in hours and minutes.

If the times are in cells A1 & B1 and you want the elapsed time in C1, follow these steps:

In cell C1, set up a custom number format of [h]:mm

Enter this formula in C1: =MAX(A1:B1)-MIN(A1:B1)

The square brackets around the h will insure that times over 24 hours are still reported as hours instead of days.

Also, the result of a date/time calculation can never be negative. I’ve used the Max and Min to make sure the earlier time is always subtracted from the later time. You could also use =ABS(B1-A1) or simply make sure that B1 will always be greater than A1.

               

Gary writes: When I copy cell(s) to a another page, then I link them, I get “0” in the empty cells were there is no data. All the cells are formatted the same. Do you know what the problem could be?

Gary is using Edit Copy, then Edit – PasteSpecial – Link Cells feature. The problem is not with the Paste Link. It is bahaving by design. If you have a range of cells, say A1:G10 and you copy, paste link, it pastes links for all the cells, even the blank ones on the theory that you may someday fill in the blank cells.

The real problem is that if you enter this simple formula in Excel, =Z1 and cell Z1 is empty, the answer to that formula is 0 instead of a blank. This can be horribly frustrating.

There is no perfect solution to this problem, but here are two ideas:

1. Make the original cells with no data contain a blank cell instead of an empty cell. For each cell that had no data, put a single apostrophe – this character: ‘ in the cell. It will not show up in the cell, but it will signal Excel that this is a blank cell, not an empty cell. The result of the link will then be blank.

2. One problem could arise: This causes the cell to no longer be considered numeric. If that empty cell is used in any formulas, you may find that the result of the formula is now #VALUE!. If this is the case and you need to leave the cell as empty, then you can use this formatting trick on the copied cells. If your current numeric format is 0.00, then set up a custom number format of

0.00;0.00;

The last semi-colon will cause cells with zero values to appear as blanks. To set up a custom number format, highlight the cells, then Format – Cells. Click the Number tab. In the Category listbox, select Custom. In the Type: box, enter the custom number format shown above.

               

From this weekend’s mailbag:

Carla writes: how do you copy & paste a subtotaled table onto a worksheet?

Let’s say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It’s a one-page summary report with data from all over heck n gone.

With ordinary copy-and-paste, the whole entire damn table gets copied. I’ve been re-tying the information most laboriously. There must be a shortcut!

Yes, Carla there is. It involves the use of the Goto-Special dialog and then selecting Visible Cells Only. All the details are in the tip of the archive. See Use Goto – Special – Visible Cells to copy just the visible cells. This is a great tip for copying the results of using Data – Subtotals.

Troy asks: Is there a way to convert a database of phone numbers that look like this

123/456-7890 or 123-456-7890

to (123) 456-7890?

Yes – Assuming your phone numbers are in A2:A4001, enter this formula in a blank B2:

=”(“&Left(A2,3)&”) “& Mid(A2,4,8)

Then copy down to B2:B4001.

Before deleting column A, you will want to change the formulas in column B to values. Highlight B2:B4001. Use Ctrl+c to Copy, then Edit – PasteSpecial – Values – OK to change the formulas to values.

Arun asks, all Excel functions – list them please

How do people end up with Excel, but not the help files? You can download a dictionary of Excel functions here:

http://homepage.ntlworld.com/noneley/

               

During college, I was on a academic track that required me to take four semesters of physics. For a year and a half, I took in the theories of physics and had everything down pretty well. Then, at the start of the 4th semester, I walked into the auditorium at Nieuwland Hall on the campus of Notre Dame for the final course in the series, Quantum Physics.

The professor walked up to the board, wrote the familiar equation that was the basis for Newtonian physics, “F=ma”. This stands for Force = Mass x Acceleration. Then, he dropped a bomb shell. “Everything that we have taught you over the last 3 semesters is wrong. F does not equal ma.” He went on to say that F=ma works for the world of bowling balls but falls apart when you get to electron-sized particles. When you get to that size particle, the equation becomes F=ma + some other thing. I never learned that part, I was too in shock that everything they had taught me was wrong.

Well, MrExcel readers, some of the things I taught you a long time ago are outdated. Working as a full-time Excel consultant, I am learning faster, easier ways of doing things all the time. Some of the weekly tips from the Tip001.shtml through Tip020.shtml were written before I learned a lot of tricks. It is somewhat embarrassing to go back and realize there are incredibly faster ways to handle certain coding situations.

When I wrote tip009.shtml about user forms in 1999, I had done a total of one user form in my life. Now, I’ve done 100’s of them and actually have a little more insight on the matter.

So, starting today, I am launching a concerted effort to modernize the old tips of the week. I will go sequentially, and will add the “Last Reviewed on ” tag after the original published date. I’ll update any links, try to make things less ambiguous, and add any new insights that a few more years of experience have provided.

               

Renato from Italy asks: How can I open a workbook in VBA choosing from a list. When I record a macro, it hard-codes the name of the selected file in the macro.

There is a command in VBA called GetOpenFileName. It displays the File Open box. You can naviagate to a directory, select the file and then click Open. At this point, the command does not open the file, it merely passes the name back to your program. Here is an example of the code in use:

Sub TestIt()

NewFN = Application.GetOpenFilename(FileFilter:=”Excel Files (*.xls), *.xls”, Title:=”Please select a file”)

If NewFN = False Then

‘ They pressed Cancel

MsgBox “Stopping because you did not select a file”

Exit Sub

Else

Workbooks.Open Filename:=NewFN

End If

End Sub

Roger asks: How can I get a macro to run before a file is closed or saved?

The macro needs to be entered on the code pane associated with “ThisWorkbook”. For a detailed view of how to do this, check out Tip 055, which discussed a Before_Print macro. Follow those instructions, but from the right dropdown, select either BeforeClose or BeforeSave.

Ken writes: I have a worksheet event handler that loops, at least 16 times. What is going on?

Ken’s handler was simple – if the entry was non-numeric it would change the entry to upper case. Here is the problem. When he changed the value of the entry to upper case, that is another worksheet change and the event would fire again. Every time the change event would fire, Ken would change the worksheet and the macro was getting called recursively, until the call stack ran out of memory.

The solution is to temporarily stop events from running while you change the value to upper case. You can do this by changing the value of Application.EnableEvents to False. Here is the corrected macro:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

If Not IsNumeric(Target.Value) Then

Application.EnableEvents = False

Target.Value = UCase(Target.Value)

Application.EnableEvents = True

End If

End Sub

Andy from the U.K. asks today’s most interesting question. I have a VBA routine in one workbook that creates several other workbooks. I would like to be able to dynamically add a hyperlink in each new workbook that will point back to the workbook that generated the new workbooks.

Andy – this is a cool idea. Without the benefit of seeing your code, I can imagine something like this would work:

Sub Testit()

Creator = ActiveWorkbook.FullName

…. Andy’s code to create the new workbook….

ActiveSheet.Hyperlinks.Add Anchor:=Range(“A1”), Address:= _

Creator, TextToDisplay:=”Click Here to Return to ” & Creator

End Sub

Long-time MrExcel readers may recall the old Challenge of the Month contest. I’ve posted a new Challenge of the Month, the first in several years. It is a tough one. Anyone is welcome to take a stab at the problem to win some cool prizes. Check it out.

Learn Excel from MrExcel