| Computing desk | ||
|---|---|---|
| < May 4 | << Apr | May | Jun >> | May 6 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
May 5 Information
I put a link to a diff in an email to myself so I could find the diff again. For some reason, Hotmail (or at least that's the address, but I'm unsure how to define Outlook.com) puts a photo representing the web site below the URL. In many cases, it's a link to a photo I want to print out later, and the photo itself appears. In this case, for some reason, I got a photo of a female UConn basketball player.— Vchimpanzee • talk • contributions • 16:44, 5 May 2022 (UTC)
- @ Vchimpanzee: It was probably because Paige Bueckers was Today's featured article for April 28, 2022. Outlook.com likely crawled Wikipedia's main page on that day and took the first image on the page to represent the website. Yeeno ( talk) 18:43, 5 May 2022 (UTC)
I have an Excel spreadsheet that lists time periods. I want them listed, in this format. For example, "4 hours and 38 minutes and 17 seconds" should be listed / formatted as 04:38:17. I want the leading zero in front of the "4" hours. In the cells, I did a "custom" format of h:mm:ss. This produced 4:38:17. There was no leading zero. I changed the "custom" format to hh:mm:ss (adding an extra "h"). This is what happened. When I looked at the spreadsheet, the hours were listed as 04:38:17 (with the leading zero, exactly what I wanted). All was fine. Then ... when I went to use the spreadsheet the next day, I noticed that the time was listed as 4:38:17 (no leading zero). I found this odd; I looked at the custom setting for the cell, and it said h:mm:ss (the leading extra "h" was missing). I tried this several times ... and, somehow, the custom format stays temporarily (the way I like it) ... and then, later, it somehow gets changed (automatically?) to the way I don't like it. What's going on? And how do I get the format I want? I also tried a custom format of [h]h:mm:ss ... with a bracket around the "h". Somewhere in the past, I was shown this method (and it worked). But, now, this custom format (with the brackets) made my Excel report an error in that cell. Thanks. Joseph A. Spadaro ( talk) 17:13, 5 May 2022 (UTC)
- Does the format change from hh to h every time you reopen the spreadsheet, or if not, when does it happen? (It seems [h] or [hh] is for hours which may exceed 24, by the way, so [h]h is a confusing thing to ask for.)
Card Zero
(talk) 17:42, 5 May 2022 (UTC)
- It's odd. If I open up the spreadsheet one or two or five or ten minutes later, the "hh" is still there. If I open up the spreadsheet a few hours later or the next day, the "hh" has completely disappeared. As far as the brackets: I forget the exact scenario, but I believe I posted on a Wiki Help Desk some time ago. I was adding up cells with time in them ... the addition was not working correctly ... and I was told to use the brackets ... and it fixed my problem (the problem I had at that time). I think ( I don't recall 100% of the details ) ... I was trying to add 20 hours plus 7 hours ... I wanted a result of 27 hours ... but I was getting 3 hours ... or some such. The 24-hour (full one-day) period was being disregarded, I think. In any event, the brackets caused no problems or errors (at that time).
Joseph A. Spadaro (
talk) 17:53, 5 May 2022 (UTC)
- For what it's worth. This was my "old" question ... from about a year ago ... about a different ( but, slightly related ) Excel scenario. This is where the idea of using "brackets" in the custom format was introduced. Wikipedia page:
[1]. Thanks.
Joseph A. Spadaro (
talk) 18:11, 5 May 2022 (UTC)
- Yes, and I expect [hh] will work here as well. I don't know if Excel will randomly change it to [h]. Possibly the change in format is triggered by Excel carrying out a calculation which exceeds 24 hours?
Card Zero
(talk) 18:15, 5 May 2022 (UTC)
- Just curious ... is [h]h a valid syntax? Or does it need to be [hh]? Or do they mean two different things? Thanks.
Joseph A. Spadaro (
talk) 18:19, 5 May 2022 (UTC)
- I have never used Excel (on purpose), I should warn you. But to my mind [h]h is specifying a two-digit number where the first digit can exceed 24, but the second can't. I suppose this would go 22, 23, 20, 21, 22, 23, 24, 25, 36, 37 ... where the "24" is composed of the first digit of 28 married to the second digit of 04. I'm not surprised that Excel refuses to do that. Card Zero (talk) 18:28, 5 May 2022 (UTC)
- Just curious ... is [h]h a valid syntax? Or does it need to be [hh]? Or do they mean two different things? Thanks.
Joseph A. Spadaro (
talk) 18:19, 5 May 2022 (UTC)
- Yes, and I expect [hh] will work here as well. I don't know if Excel will randomly change it to [h]. Possibly the change in format is triggered by Excel carrying out a calculation which exceeds 24 hours?
Card Zero
(talk) 18:15, 5 May 2022 (UTC)
- For what it's worth. This was my "old" question ... from about a year ago ... about a different ( but, slightly related ) Excel scenario. This is where the idea of using "brackets" in the custom format was introduced. Wikipedia page:
[1]. Thanks.
Joseph A. Spadaro (
talk) 18:11, 5 May 2022 (UTC)
- It's odd. If I open up the spreadsheet one or two or five or ten minutes later, the "hh" is still there. If I open up the spreadsheet a few hours later or the next day, the "hh" has completely disappeared. As far as the brackets: I forget the exact scenario, but I believe I posted on a Wiki Help Desk some time ago. I was adding up cells with time in them ... the addition was not working correctly ... and I was told to use the brackets ... and it fixed my problem (the problem I had at that time). I think ( I don't recall 100% of the details ) ... I was trying to add 20 hours plus 7 hours ... I wanted a result of 27 hours ... but I was getting 3 hours ... or some such. The 24-hour (full one-day) period was being disregarded, I think. In any event, the brackets caused no problems or errors (at that time).
Joseph A. Spadaro (
talk) 17:53, 5 May 2022 (UTC)
- Excel's custom format's have a tendency to not "stick"; I run into it all the time in my spreadsheets built to show "thousands of dollars". Are you sure that none of the default time formats will do? In my version (Excel 365), there are five default options: three AM/PM and two 24-hour time. One of the 24-hour options has the leading zero you want, though obviously 1 PM will show as 13:00:00. If that's acceptable, the default formats are much less likely to break. 70.24.163.91 ( talk) 02:30, 12 May 2022 (UTC)
- I haven't tried to reproduce your issue, as it would take several hours, but is it maybe possible to do the formatting using the
TEXTfunction? So you hide the actual time field, and instead you have a displayed field with=TEXT(B1,"hh:mm:ss"). El sjaako ( talk) 11:33, 12 May 2022 (UTC)
- That does seem to work and since it's not a customer format it should stick, but it does have the drawback that the resultant field is no longer a value - it's text, which can't get math done to it. It also registers as 24-hour time format, so I would still recommend my suggestion above since the results look the same but leave the cell as a value (and don't take up an extra cell), but with Excel it's always good to have a few options available. :-) 70.24.163.91 ( talk) 23:39, 12 May 2022 (UTC)
| Computing desk | ||
|---|---|---|
| < May 4 | << Apr | May | Jun >> | May 6 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
May 5 Information
I put a link to a diff in an email to myself so I could find the diff again. For some reason, Hotmail (or at least that's the address, but I'm unsure how to define Outlook.com) puts a photo representing the web site below the URL. In many cases, it's a link to a photo I want to print out later, and the photo itself appears. In this case, for some reason, I got a photo of a female UConn basketball player.— Vchimpanzee • talk • contributions • 16:44, 5 May 2022 (UTC)
- @ Vchimpanzee: It was probably because Paige Bueckers was Today's featured article for April 28, 2022. Outlook.com likely crawled Wikipedia's main page on that day and took the first image on the page to represent the website. Yeeno ( talk) 18:43, 5 May 2022 (UTC)
I have an Excel spreadsheet that lists time periods. I want them listed, in this format. For example, "4 hours and 38 minutes and 17 seconds" should be listed / formatted as 04:38:17. I want the leading zero in front of the "4" hours. In the cells, I did a "custom" format of h:mm:ss. This produced 4:38:17. There was no leading zero. I changed the "custom" format to hh:mm:ss (adding an extra "h"). This is what happened. When I looked at the spreadsheet, the hours were listed as 04:38:17 (with the leading zero, exactly what I wanted). All was fine. Then ... when I went to use the spreadsheet the next day, I noticed that the time was listed as 4:38:17 (no leading zero). I found this odd; I looked at the custom setting for the cell, and it said h:mm:ss (the leading extra "h" was missing). I tried this several times ... and, somehow, the custom format stays temporarily (the way I like it) ... and then, later, it somehow gets changed (automatically?) to the way I don't like it. What's going on? And how do I get the format I want? I also tried a custom format of [h]h:mm:ss ... with a bracket around the "h". Somewhere in the past, I was shown this method (and it worked). But, now, this custom format (with the brackets) made my Excel report an error in that cell. Thanks. Joseph A. Spadaro ( talk) 17:13, 5 May 2022 (UTC)
- Does the format change from hh to h every time you reopen the spreadsheet, or if not, when does it happen? (It seems [h] or [hh] is for hours which may exceed 24, by the way, so [h]h is a confusing thing to ask for.)
Card Zero
(talk) 17:42, 5 May 2022 (UTC)
- It's odd. If I open up the spreadsheet one or two or five or ten minutes later, the "hh" is still there. If I open up the spreadsheet a few hours later or the next day, the "hh" has completely disappeared. As far as the brackets: I forget the exact scenario, but I believe I posted on a Wiki Help Desk some time ago. I was adding up cells with time in them ... the addition was not working correctly ... and I was told to use the brackets ... and it fixed my problem (the problem I had at that time). I think ( I don't recall 100% of the details ) ... I was trying to add 20 hours plus 7 hours ... I wanted a result of 27 hours ... but I was getting 3 hours ... or some such. The 24-hour (full one-day) period was being disregarded, I think. In any event, the brackets caused no problems or errors (at that time).
Joseph A. Spadaro (
talk) 17:53, 5 May 2022 (UTC)
- For what it's worth. This was my "old" question ... from about a year ago ... about a different ( but, slightly related ) Excel scenario. This is where the idea of using "brackets" in the custom format was introduced. Wikipedia page:
[1]. Thanks.
Joseph A. Spadaro (
talk) 18:11, 5 May 2022 (UTC)
- Yes, and I expect [hh] will work here as well. I don't know if Excel will randomly change it to [h]. Possibly the change in format is triggered by Excel carrying out a calculation which exceeds 24 hours?
Card Zero
(talk) 18:15, 5 May 2022 (UTC)
- Just curious ... is [h]h a valid syntax? Or does it need to be [hh]? Or do they mean two different things? Thanks.
Joseph A. Spadaro (
talk) 18:19, 5 May 2022 (UTC)
- I have never used Excel (on purpose), I should warn you. But to my mind [h]h is specifying a two-digit number where the first digit can exceed 24, but the second can't. I suppose this would go 22, 23, 20, 21, 22, 23, 24, 25, 36, 37 ... where the "24" is composed of the first digit of 28 married to the second digit of 04. I'm not surprised that Excel refuses to do that. Card Zero (talk) 18:28, 5 May 2022 (UTC)
- Just curious ... is [h]h a valid syntax? Or does it need to be [hh]? Or do they mean two different things? Thanks.
Joseph A. Spadaro (
talk) 18:19, 5 May 2022 (UTC)
- Yes, and I expect [hh] will work here as well. I don't know if Excel will randomly change it to [h]. Possibly the change in format is triggered by Excel carrying out a calculation which exceeds 24 hours?
Card Zero
(talk) 18:15, 5 May 2022 (UTC)
- For what it's worth. This was my "old" question ... from about a year ago ... about a different ( but, slightly related ) Excel scenario. This is where the idea of using "brackets" in the custom format was introduced. Wikipedia page:
[1]. Thanks.
Joseph A. Spadaro (
talk) 18:11, 5 May 2022 (UTC)
- It's odd. If I open up the spreadsheet one or two or five or ten minutes later, the "hh" is still there. If I open up the spreadsheet a few hours later or the next day, the "hh" has completely disappeared. As far as the brackets: I forget the exact scenario, but I believe I posted on a Wiki Help Desk some time ago. I was adding up cells with time in them ... the addition was not working correctly ... and I was told to use the brackets ... and it fixed my problem (the problem I had at that time). I think ( I don't recall 100% of the details ) ... I was trying to add 20 hours plus 7 hours ... I wanted a result of 27 hours ... but I was getting 3 hours ... or some such. The 24-hour (full one-day) period was being disregarded, I think. In any event, the brackets caused no problems or errors (at that time).
Joseph A. Spadaro (
talk) 17:53, 5 May 2022 (UTC)
- Excel's custom format's have a tendency to not "stick"; I run into it all the time in my spreadsheets built to show "thousands of dollars". Are you sure that none of the default time formats will do? In my version (Excel 365), there are five default options: three AM/PM and two 24-hour time. One of the 24-hour options has the leading zero you want, though obviously 1 PM will show as 13:00:00. If that's acceptable, the default formats are much less likely to break. 70.24.163.91 ( talk) 02:30, 12 May 2022 (UTC)
- I haven't tried to reproduce your issue, as it would take several hours, but is it maybe possible to do the formatting using the
TEXTfunction? So you hide the actual time field, and instead you have a displayed field with=TEXT(B1,"hh:mm:ss"). El sjaako ( talk) 11:33, 12 May 2022 (UTC)
- That does seem to work and since it's not a customer format it should stick, but it does have the drawback that the resultant field is no longer a value - it's text, which can't get math done to it. It also registers as 24-hour time format, so I would still recommend my suggestion above since the results look the same but leave the cell as a value (and don't take up an extra cell), but with Excel it's always good to have a few options available. :-) 70.24.163.91 ( talk) 23:39, 12 May 2022 (UTC)