Top Choices for November 2009...

Time for an Excel Time Trick

One of the things that I've been doing lately with my local computer club is to maintain its calendar of events on its web site for the workshops, classes and special interest group meetings. For an all-volunteer organization, we have a lot of fun and share a lot of knowledge in our 40-60 educational events each month (March has 62 events!).

Our instructor coordinator handles the schedule of what's being taught, when, by whom, and in which classroom or lab — and keeps it all in an Excel spreadsheet. That way, he can easily add lines for additional events, sort, and circulate the schedule to the instructors for review before releasing it. Of course, any instructor who does not have Excel can use the "Calc" spreadsheet that's part of the free open-source OpenOffice.org office suite.

For our online calendar of events, I use Excel to take the values he has in different rows and columns, including blank spacing lines, and embed that data into the HTML code to create the table to be displayed on our user group's web site. I paste individual columns of data from his spreadsheet into my spreadsheet. Also in my spreadsheet, I have HTML code to use in creating the table rows and the cells in each row. Then, I use Excel's Concatenate function, once for each row, to combine all the HTML strings and the data into the HTML code that I paste into the web page.

Tech Tip
  What's the Concatenate function? It's similar to addition of numbers, but instead, it allows you to   combine multiple text values into a single cell.   Let's look at it in an example:

     Cell A1 has the text string "This is" as its value
     Cell B1 has the value "a test."

  You can combine them in Cell C1 (or any other cell) using the following formula:
     =Concatenate(A1;" ";B1)
  to get the result "This is a test."
  You can also use a more logical
     =A1&" "&B1

The problem is that Excel stores dates and times in numeric format. Excel is very emphatic about that, too. If cell has the default General format, when you type 9:00 AM into it, Excel promptly converts the value to 0.375 and displays it to a Time format. When you copy and paste that time within the same spreadsheet, Excel copies the number 0.375 and the format h:mm AM/PM, so all looks perfect. But, when you try to use that 9:00 AM value in a calculation or copy it to the Windows Clipboard to copy it into another application, Excel uses as 0.375.

So, when I concatenated the corresponding cells to display the schedule, the times of the classes and workshops all showed as their numeric equivalents! I tried the Text() conversion, but, every time the value was calculated, Excel would store it as a value. Of course, if I set the format of the cell to Text, it would show me the FORMULA instead of calculating it. My "final" first solution was to add a column where I set the format as Text and then typed in the times (Text will store the value as text, no matter what you type).

The real trick was to cheat — to force Excel initially to handle the time as a text value, and then, when I wanted to use the value, to change it back.

The solution was to insert a space at the beginning of the string, so Excel would not convert it back into a number.
   Cell G15 has 12:00 PM as its value, which Excel has stored as the number 0.5 and displays in Time format "h:mm AM/PM".
   H15 =IF(ISBLANK(G15)," ",CONCATENATE(" "&TEXT(G15,"h:mm AM/PM")))
and then, when I want to use the value, I remove the space by grabbing all but the leftmost character (the space)
   RIGHT(H15,LEN(H15)-1)
as I generate the HTML for the web page.

The full cell formula I used to at that point for cell L15 was
=IF(ISBLANK(C15),
D15&E15&$F$5&F15&$H$5&RIGHT(H15,LEN(H15)-1)&$I$5&I15&$J$5&J15&$K$5&K15&$L$5,
D15&E15&$F$4&F15&$H$4&RIGHT(H15,LEN(H15)-1)&$I$4&I15&$J$4&J15&$K$4&K15&$L$4)
[if the lines starting with D15 wrap, that's your email program doing it - open the email to full screen and you will see that the whole formula is spread across three lines]

If you use Excel much, you know that as you type in the formula, Excel puts into one long, hard-to-read line. But, guess what? Excel stores and displays that formula with the carriage returns, which makes it immensely easier to understand.

I've even got spreadsheets where I use the maximum number of nested IF statements (which is 7) — and tricking Excel into displaying the formula this way helps ME write the formulas without getting confused.

Want to know how? Read this week's online newsletter to learn this handy Excel trick.

Thank you for visiting my site — I hope you found the site and articles helpful. If you did, please consider supporting my efforts by making a purchase (if you have one to make) via one of the links in my articles, one of my recommendations, or in my "Ads by Terry" to purchase the item. You can also shop via these links to major Internet retailers
Amazon.com, Buy.com and NewEgg.com or this Shopping page...

Time for an Excel Time Trick

Computer Tips

More Tips

Free Newsletters

Shopping

About This Site