Wikipedia:Reference desk/Archives/Computing/2015 January 17

= January 17 =

How do you format characters to look like Scrabble tiles?
I want to format some text to put each character in its own bordered box, making the characters look kind of like individual Scrabble tiles (without the letter values). Is there an easy way to do this in HTML+CSS, LaTeX, or Word? (What I'm trying to do is to use the formatting to distinguish sentences in an object language from text in the meta-language, and to visually emphasize that the sentences in the object language are strings of symbols.) Thanks. --96.227.60.16 (talk) 13:33, 17 January 2015 (UTC)


 * As is done in key press? --  Gadget850talk 15:51, 17 January 2015 (UTC)


 * Something like that, except that the boxes are around each character individually. Is there a way to do that without making each character a separate element? --96.227.60.16 (talk) 16:12, 17 January 2015 (UTC)


 * Is there a Unicode range of boxed letters? —Tamfang (talk) 16:58, 17 January 2015 (UTC)


 * Have you tried to Google "scrabble font"...? --CiaPan (talk) 17:12, 17 January 2015 (UTC)


 * I did search for fonts that look like Scrabble tiles, but the ones I've found had problems: I don't want the Scrabble letter value included as part a character and I don't need the boxes to actually look like wooden tiles. Something that renders like
 * f(x,y)
 * would be perfect. --96.227.60.16 (talk) 17:40, 17 January 2015 (UTC)
 * I haven't tried it yet, but here are a few methods of how to do something like that in LaTeX:, . SemanticMantis (talk) 17:34, 18 January 2015 (UTC)
 * Searching the web for css letter box seems to suggest "can't be done with CSS alone." Someone suggests using JavaScript to turn each letter into a separate &lt;span&gt; with a style that draws a border. 88.112.50.121 (talk) 20:40, 18 January 2015 (UTC)


 * → f(x,y)
 * And here has CSS that adds a box. --   Gadget850talk 21:13, 18 January 2015 (UTC)


 * http://www.fonts101.com/fonts/view/Letter_Bats/10023/Boxed_In is a font with each character surrounded by a box. It is free, and it is possible to instruct a webbrowser to use fonts stored on the webserver. Of course, the other way to do it is to convert the tiles to images, and display them. However that isn't easily blind-accessible, without using a different CSS layout for screen-readers. LongHairedFop (talk) 11:36, 19 January 2015 (UTC)

Formatting time durations (minutes and seconds) in Excel 2013
Does anyone know how to format minutes and seconds in Excel 2013? For example, let's say that I have two cells. In the each cell, I want to enter the value for one-and-a-half minutes. So, in each cell, I want to type in 1:30 (for 1 minute and 30 seconds). (I don't want to type in 1.5 for the value.) I want the value in each cell to display as "1:30" (without the quotation marks). If I add the two cells together, I want to get a result that reads "3:00" (representing the total time of 3 minutes and 0 seconds). This seems like it should be easy to do, but I cannot seem to do it. In its drop-down menu, Excel even has a "number formatting" option called "mm:ss" (for minutes and seconds). But, when I use that, it seems to think that my values are dates. For example, if I enter "1:30", it seems to think that that means "January 1, 1900, 1:30 in the AM" (or some such). Then, of course, the addition results are incorrect. Any help? Ultimately, what I am trying to do is this: I have a long column of time durations, listed as "mm:ss" format, and I want to add them up to calculate the total time. So, for a very easy example, I might have six rows in the column: 0:10 and 0:10 and 0:10 and 0:10 and 0:10 and 0:10. And I want the computer (Excel) to add these up and tell me that the total time duration is "1:00" minutes. Thanks. Joseph A. Spadaro (talk) 16:16, 17 January 2015 (UTC)


 * I tried playing around with this myself and I see what you mean. I tried searching "recording lap times in excel" or similar. I came across this, wade through it and see what works for you: Entering Times into Excel RegistryKey(RegEdit) 17:57, 17 January 2015 (UTC)


 * Thanks. I will check out that link.  It looks like it may be helpful.  I only did a quick scan.  But, it also looks like time calculations are more complex than I had imagined.  It seems you can't just simply type in a "1:30" for "one minute and 30 seconds", as one would have expected.   Joseph A. Spadaro (talk) 19:07, 17 January 2015 (UTC)


 * You may find this gets more difficult as you try to add times that are over 24 hours in total, but the first thing to do is to get used to the fact that Excel requires times to be added as hh:mm:ss; so for your one minute 30 you must enter it as 0:1:30. That's actually quite reasonable: it otherwise can't guess that 1:30 is one minute 30 seconds or one hour 30 minutes.--Phil Holmes (talk) 18:17, 17 January 2015 (UTC)


 * Hmmmmmmm. Interesting.  First, I never thought to try the 0:1:30 format (i.e., adding in a place-holder for "zero hours").  I will indeed try that and see what happens.  However, as to your second point.  You state: "it otherwise can't guess that 1:30 is one minute 30 seconds or one hour 30 minutes".  In theory, I would agree with you.  However, Excel has a very specific "number format" that says "mm:ss".  They also have various other "number formats", involving the "h" for hours (for example, there is a format "hh:mm:ss").  So, it would seem to me that Excel should "know" that "01:30" means minutes and seconds (and not hours and minutes) when I specifically select the "mm:ss" format option.  In any event, I will try your suggestion with adding in the "hours" value.  Thanks.   Joseph A. Spadaro (talk) 18:48, 17 January 2015 (UTC)


 * No, I still cannot get it to work. This is the problem, as I see it.  When I enter a value such as "0:1:30", I am interpreting that to mean "0 hours and 1 minute and 30 seconds".  For whatever crazy reason, Excel interprets that as "January 1, 1900, 1:30 AM" (or something like that).  Which is odd.  Because I am using the options under time formats, not date formats.  Help?   Joseph A. Spadaro (talk) 18:55, 17 January 2015 (UTC)


 * Try using the "custom" setting. It works for me in my old version of Excel.    D b f i r s   19:36, 17 January 2015 (UTC)


 * I thought I was in the "custom" setting. Which custom setting are you referring to?   Thanks.   Joseph A. Spadaro (talk) 19:49, 17 January 2015 (UTC)


 * My version of Excel has a separate "Custom" category under Format -> Cells -> (Number Tab). This seems to work differently from the general time setting.  I don't have a modern version of Excel to compare, but mine adds minutes and seconds quite happily with the mm:ss format, though, as Phil says above, you do have to include the 0: hours when entering data, and, if the total is over 60 minutes you need the sum to be formatted as [h]:mm:ss.  Microsoft don't usually remove functionality, but just occasionally they do, so perhaps someone else with a version of Excel written in the last fifteen years can check?    D b f i r s   20:39, 17 January 2015 (UTC)


 * Yes, that is exactly what I am using. I go to Number; then Custom.  In the Custom list, there are many options.  Many of them contain the "mm:ss" and "h:mm:ss" types of notations.  And those are the ones that I am using and have been referring to above.   Joseph A. Spadaro (talk) 01:31, 18 January 2015 (UTC)


 * One solution I just thought of is to have a hidden math sheet elsewhere that crunches the raw seconds only values for each field, eg hours and minutes, then use reference calls to display it on your visible work area, such as cell A1 might actually have a formula that reads "=E110" where E110 stores all the seconds values with appropriate math to remove anything greater than or equal to 60 to the minutes cell, which might be F110. Then via the CONCATENATE function, see here, you can make it show like 24:56, where "24" is actually coming from cell F110 and "56" is coming from cell E110. The concatenate function allows you to reference the contents of two cells into one destination cell. RegistryKey(RegEdit) 23:08, 17 January 2015 (UTC)

Wow, this is far more complicated than I had anticipated. I thought that this would be a pretty simple, basic, and straight-forward Excel function. Hard to believe that it's not. Joseph A. Spadaro (talk) 01:34, 18 January 2015 (UTC)
 * See these results  from a simple search for 'excel entering time mm:ss'. I'm not quite sure how the date thing arose but that aside, I think the problem is there are 2 different things being conflated here. One is how Excel displays the data. The other is how Excel interprets the data. Excel always interprets time entered in the format of x:xx as h:mm and it doesn't sound like there's a simple way to change this. It doesn't matter how you tell it to display the data. There are various hackish workarounds you can use if you really need to enter data in the format mm:ss such as that suggest above of those found from the simple search. Nil Einne (talk) 07:24, 18 January 2015 (UTC)

Thanks, all. I finally got it to work (I think). I entered all the times in hh:mm:ss format. So, for 1 minute and 30 seconds, I enter it as "0:1:30". That is how you have to enter it. Then, with the "Custom" options (under "Number"), I can have it display as either "0:1:30" (with the zero hours) or as "1:30" (without the zero hours). And this all seems to work. And the mathematical calculations of addition, sum, total, etc., all seem to work. There are two "odd" things that I did notice, however. (1) I had to format the cells before I entered the data, for some odd reason. So, I had to select the "Custom" format of "mm:ss" on a range of empty/blank cells. And, then, I had to subsequently go into those blank cells and enter my data (as h:mm:ss). When I did it in this order, it seemed to work fine. And (2): I don't know whether or not this is important. It does not seem to affect the output display or the math calculations. But, for whatever odd reason, Excel is still interpreting all of these data as actual dates, not time durations. So, when I enter "0:1:30", it will display as "1:30". But, when I go and look at the "formula bar" (I think that's what it's called), to see what value is in that cell, Excel has "January 1, 1900, 1:30 AM" or something like that. Which is odd. But, it doesn't seem to affect the output display or the calculations, from what I can see. I guess (?) that internally, when Excel sees a data entry of "0:1:30", Excel interprets that as "1:30 AM on January 1, 1900". Anyways, thanks. It works for me and it does what I needed it to do. Namely, add up a bunch of individual time durations to give me the total time duration. Thanks. Joseph A. Spadaro (talk) 16:48, 18 January 2015 (UTC)
 * Excel doesn't have a concept of tme or date. It works in "days" - so 0 is midnight at the start of the zeroth day - the start of the epoch. If you add 365 to that you will get (leap year permitting) the same date and time one year later. An hour is =1/24 or 0.041666, a minute is 1/(24*60), a second is 1/(24*60*60) or 0.00001157. When you enter 0:1:10, you are entering a time (in days) 1 minute 10 seconds after the start of the epoch or 0.00081018. -- SGBailey (talk) 11:42, 19 January 2015 (UTC)
 * Some date/time calculation modules -- though it sounds like Excel's isn't one of them -- distinguish between absolute timestamps versus relative durations. As a comment in mine says,
 * An absolute time is one which is anchored to some actual calendar date, while a relative time records an abstract amount of time (which might, for example, be a difference between two absolute times).
 * Building further on that parenthetical, a timestamp minus a timestamp yields a duration; a timestamp plus or minus a duration yields a timestamp; a duration plus or minus a duration yields a duration; a timestamp plus a timestamp is meaningless. (A duration times or divided by a scalar yields a duration; a timestamp times or divided by a scalar is meaningless.)  Joseph Spadaro clearly wanted to add up a bunch of durations, so if Excel deals only in timestamps, as SGBailey describes, I'm surprised it could ever be made to work.  (But all's well that ends well!) —Steve Summit (talk) 12:55, 19 January 2015 (UTC)


 * Well Excel stores both dates and intervals as real numbers and interprets both as timestamps except when formatted without the date, so the difficulty is in entering intervals of more than 23 hours 59 minutes and 59 seconds. Addition, multiplication and division by positive real numbers work just fine, as does subtraction of smaller intervals from larger ones, and the result can be displayed in dd:hh:mm:ss.  Once the interval range gets into months and days, the interpretation routine in Excel causes problems.    D b f i r s   23:35, 19 January 2015 (UTC)
 * Excel limits (wraps?) after 23:59 with format hh:mm, but with format [hh]:mm the hours keep counting. You can type in 25:30 for 1 day1 hour 30 minutes and "normally" it will display as 1:30. If you format the inpu cell and any derived output cells as [hh]:mm all is fine. -- SGBailey (talk) 11:59, 20 January 2015 (UTC)
 * Yes, the actual storage is identical (as the number 1.06) so it doesn't wrap in storage, only in the display (because it interprets the input as 1:30 a.m. on the first day of the epoch) (January 1st 1900). When the "day" (integer part) is zero, Excel seems to treat the input as pure interval. The format can be extended to dd-hh:mm:ss but I'm not sure how reliable this is.   D b f i r s   17:49, 20 January 2015 (UTC)

Thanks, all. Joseph A. Spadaro (talk) 19:12, 20 January 2015 (UTC)